/*
 * ProductAvailabilityAction.java
 *
 * Created on June 9, 2002, 9:11 AM
 */

package com.scalar.forecast.forecastReports.actions;


import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.commons.Labels;
import com.scalar.chiptrack.operations.process.dao.NewProductDAO;
import com.scalar.chiptrack.operations.process.dao.ProductDao;
import com.scalar.chiptrack.operations.reports.DataDumpReportsHelper;
import com.scalar.chiptrack.sales.dao.CustomerDAO;
import com.scalar.chiptrack.users.Functionality;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.utils.DateUtil;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.forecast.ForecastExtendedAction;
import com.scalar.forecast.commons.SessionKeys;
import com.scalar.forecast.forecastReports.AnalysisReport;
import com.scalar.forecast.forecastReports.dao.ForecastReportsDAO;
import com.scalar.forecast.forecastReports.forms.SalesForecastReportForm;
import com.scalar.forecast.projectSetup.dao.UserDefinedFieldsDAO;
import com.scalar.forecast.salesForecastEntry.AutoGrowingLinkedList;
import com.scalar.forecast.salesForecastEntry.RowData;
import com.scalar.forecast.salesForecastEntry.SlotData;
import com.scalar.forecast.salesForecastEntry.dao.SalesForecastEntryDAO;
import com.scalar.forecast.salesPersonSetup.dao.SalesPersonSetupDAO;
import com.scalar.ScalarException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.util.LabelValueBean;
import org.apache.struts.util.MessageResources;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import java.util.HashMap;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.LinkedList;
import java.util.Map;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;


public class SalesForecastReportAction extends ForecastExtendedAction implements SessionKeys
{
    /**
     * constant indicating the report type revenue. This value is used in water fall chart report.
     */
    private final String REPORT_TYPE_REVENUE = "revenue";

    /**
     * constant indicating the report type quantity. This value is used in water fall chart report.
     */
    private final String REPORT_TYPE_QUANTITY = "quantity";

    public ActionForward executeRequest( ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response ) throws Exception
    {

        String action = request.getParameter("action");
        HashMap<String, String> errors = new HashMap<String, String>();
		request.setAttribute (SessionKeys.ERROR_MESSAGES, errors);

		HashMap info = new HashMap();
		request.setAttribute (SessionKeys.INFO_MESSAGES, info);

        SalesForecastReportForm salesForecastReportForm = (SalesForecastReportForm) form;
        HttpSession session     = request.getSession();
        User pUser = (User)session.getAttribute ( SessionKeys.USER_KEY );
        int spersonId   = -1;
        Workbook workbook = null;
        String fileName = "";
        try
        {
            spersonId  = Integer.parseInt ( (String)session.getAttribute ( SessionKeys.USER_SALESPERSON_ID ) );
        }
        catch ( NumberFormatException e )
        {
              spersonId = -1;
        }
        if ( "exportToExcel".equalsIgnoreCase( action ) )
        {
            if( "forecast".equalsIgnoreCase(salesForecastReportForm.getReportType()))
            {
                //workbook = getForecastReportWorkBookNewFormat ( salesForecastReportForm );
                workbook = getForecastReportWorkBook( salesForecastReportForm );
                fileName = "Forecast Report_"+ DateUtil.getTimeStamp() ;
            }
            else
            {
                workbook = getForecastBacklogReportWorkBook ( salesForecastReportForm );
                fileName = "ForecastVs Backlog Report_"+ DateUtil.getTimeStamp() ;
            }

            ExcelUtils.generateExcelReport( workbook, fileName, response );

            return null;
        }
        else if ("leftToBookReport".equalsIgnoreCase(action ) )
        {
            request.setAttribute( "mktPartNumberList", NewProductDAO.loadMarketingPartNumbersbyProductID( null, Constants.LOCATION_TYPE_FG, "0" ) );
            request.setAttribute( "chipSetList" , NewProductDAO.getProcessParameterValueList(null, "ChipSET1") );
            request.setAttribute( "chipFamilyList" , NewProductDAO.getProcessParameterValueList(null, "Chip Family") );
            request.setAttribute( "customersList",  ForecastReportsDAO.getAllCustomers( null ) );
            request.setAttribute( "quaterList",  DateUtil.loadQuarterList() );
            request.setAttribute( "yearList",  DateUtil.getYearList() );

            return (mapping.findForward ("leftToBookReport"));
        }
        else if ("xlsLeftToBookReport".equalsIgnoreCase(action ) )
        {
            String reportType = request.getParameter("reportType");
            String forecastType = request.getParameter("forecastType");

            ArrayList leftToBookList = ForecastReportsDAO.getLeftToBookReport ( null, salesForecastReportForm, reportType, forecastType );

            workbook =  DataDumpReportsHelper.getWorkbook( leftToBookList, "Left To Book "+ reportType +"("+ forecastType + ") Report", null, null, null );
            //workbook = DataDumpReportsHelper.formatWipBacklog( workbook );
            fileName = "Left To Book "+ reportType + "("+ forecastType + ") Report-" + DateUtil.getTimeStamp() ;

            //To GENERATE THE EXCEL REPORT
            ExcelUtils.generateExcelReport( workbook, fileName, response );

        }
        else if ("forecastAnalysis".equalsIgnoreCase(action ) )
        {
            request.setAttribute( "partNumberList", NewProductDAO.loadPartNumbers( null, 1, Constants.LOCATION_TYPE_FG, true, true, true, 0 ) );
            request.setAttribute( "baseProductList", NewProductDAO.loadAllBaseProducts( null, true, true ) );
            request.setAttribute( "salesPersonList" , SalesPersonSetupDAO.getSalesPersonList() );
            request.setAttribute( "customerList",  CustomerDAO.getAllCustomerNames());
            request.setAttribute( "yearList",  DateUtil.getYearList() );
            request.setAttribute( "monthsList", SalesForecastEntryDAO.getMonthsList() );

            Calendar fromDate = Calendar.getInstance();
            
            salesForecastReportForm.setFromMonth(fromDate.get(Calendar.MONTH));
            salesForecastReportForm.setYear(fromDate.get(Calendar.YEAR));
            fromDate.add(Calendar.MONTH, 11);
            salesForecastReportForm.setToMonth(fromDate.get(Calendar.MONTH));
            salesForecastReportForm.setToYear(fromDate.get(Calendar.YEAR));
            salesForecastReportForm.setProductIds(new int[]{0});
            salesForecastReportForm.setProcessStepIds(new int[]{0});
            salesForecastReportForm.setSalesPersonIds(new int[]{0});
            salesForecastReportForm.setCustomerIds(new int[]{0});

            return (mapping.findForward ("forecastAnalysis"));
        } else if ("forecastWaterFallSearch".equalsIgnoreCase(action)) {
            request.setAttribute("partNumberList", NewProductDAO.loadPartNumbers(null, 1, Constants.LOCATION_TYPE_FG, true, true, true, 0));
            request.setAttribute("salesPersonList", SalesPersonSetupDAO.getSalesPersonList());
            request.setAttribute("customerList", CustomerDAO.getAllCustomerNames());

            salesForecastReportForm.setReportType(REPORT_TYPE_QUANTITY);
            salesForecastReportForm.setProductIds(new int[]{0});
            salesForecastReportForm.setProcessStepIds(new int[]{0});
            salesForecastReportForm.setSalesPersonIds(new int[]{0});
            salesForecastReportForm.setCustomerIds(new int[]{0});

            return (mapping.findForward("forecastWaterFallSearch"));
        } else if ("xlsforecastWaterFallChart".equalsIgnoreCase(action)) {
            int projId = -1;
            int prodId = salesForecastReportForm.getProcessStepIds()[0];
            int custId = salesForecastReportForm.getCustomerIds()[0];
            int salesPersonId = salesForecastReportForm.getSalesPersonIds()[0];
            String reportType = salesForecastReportForm.getReportType();
            int showNext = 7;
            int showNextFCDetails = 18;
            Calendar cal = Calendar.getInstance();
            cal.add(Calendar.MONTH, -6); //we need to show the forecast details from past six months.
            int startMonth = cal.get(Calendar.MONTH);
            int startYear = cal.get(Calendar.YEAR);

            LinkedList rowDataList = SalesForecastEntryDAO.getWaterFallChartRowDataList(null, projId, prodId, custId, salesPersonId, startMonth, startYear, showNext, showNextFCDetails);

            workbook = generateWaterFallChart(rowDataList, salesForecastReportForm, "Forecast Water Fall Chart", startMonth, startYear, showNextFCDetails);
            fileName = "Forecast Water Fall Chart_"+ DateUtil.getTimeStamp() ;
            ExcelUtils.generateExcelReport( workbook, fileName, response );

            return null;
        }
        else if ("xlsforecastAnalysis".equalsIgnoreCase(action ) )
        {
            request.setAttribute( "partNumberList", NewProductDAO.loadPartNumbers( null, 1, Constants.LOCATION_TYPE_FG, true, true, true, 0 ) );
            request.setAttribute( "baseProductList", NewProductDAO.loadAllBaseProducts( null, true, true ) );
            request.setAttribute( "salesPersonList" , SalesPersonSetupDAO.getSalesPersonList() );
            request.setAttribute( "customerList",  CustomerDAO.getAllCustomerNames());
            request.setAttribute( "yearList",  DateUtil.getYearList() );
            request.setAttribute( "monthsList", SalesForecastEntryDAO.getMonthsList() );

            if(salesForecastReportForm.isStartOver()){
                session.removeAttribute("forecastDataList");
            }

            LinkedList forecastDataList = null;
            String searchParams = null;
            if( session.getAttribute( "forecastDataList" ) == null ) 
            {
            	forecastDataList = ForecastReportsDAO.getSalesForecastReportRowDataListForAnalysis();
            	System.out.println( "Forecast Data List " + forecastDataList.size() );
            }
            else
            {
            	forecastDataList = (LinkedList)session.getAttribute( "forecastDataList" );
                searchParams = (String)session.getAttribute ( "searchParams" );
            }
            String paramsStr = prepareSearchParamsStr(salesForecastReportForm);
            searchParams = searchParams==null? paramsStr : searchParams + "#" + paramsStr;

            applyAnalysis( forecastDataList, paramsStr );
            //LinkedList<RowData> selectedList = getSelectedList(forecastDataList);
            workbook =  generateWorkBook( forecastDataList );
            fileName = "Forecast Analysis Report_"+ DateUtil.getTimeStamp() ;
            ExcelUtils.generateExcelReport( workbook, fileName, response );
            session.setAttribute( "forecastDataList" , forecastDataList );
            session.setAttribute( "searchParams", searchParams );
            return (mapping.findForward ("forecastAnalysis"));
        }
        else if ("saveForecastAnalysis".equalsIgnoreCase(action ) )
        {
            request.setAttribute( "partNumberList", NewProductDAO.loadPartNumbers( null, 1, Constants.LOCATION_TYPE_FG, true, true, true, 0 ) );
            request.setAttribute( "baseProductList", NewProductDAO.loadAllBaseProducts( null, true, true ) );
            request.setAttribute( "salesPersonList" , SalesPersonSetupDAO.getSalesPersonList() );
            request.setAttribute( "customerList",  CustomerDAO.getAllCustomerNames());
            request.setAttribute( "yearList",  DateUtil.getYearList() );
            request.setAttribute( "monthsList", SalesForecastEntryDAO.getMonthsList() );

            String searchParams = (String)session.getAttribute( "searchParams");
            if( searchParams == null || searchParams.trim().length() == 0 ) {
                errors.put("Please generate the report by clicking the Submit and then click Save to save the generated report.", null);
            } else if( salesForecastReportForm.getReportName() == null || salesForecastReportForm.getReportName().trim().length()==0 ) {
                errors.put ( "Report name required.", null );
            } else {
                try {
                    ForecastReportsDAO.saveForecastAnalysisReport(null, salesForecastReportForm.getScenario(), salesForecastReportForm.getReportName().trim(), searchParams);
                    info.put("Forecast Analysis report saved successfully",null);
                } catch ( Exception e ){
                    e.printStackTrace();
                    errors.put ( "Could not save Forecast Analysis report", e.getMessage());
                }
            }

            return (mapping.findForward ("forecastAnalysis"));
        } else if ( "forecastAnalysisViewAndCompare".equalsIgnoreCase(action)) {

            ArrayList<AnalysisReport> analysisReportsList = ForecastReportsDAO.getForecastAnalysisReportsList(null, null, null, null, 0, 0);

            request.setAttribute("analysisReportsList", analysisReportsList);
            return mapping.findForward ("forecastAnalysisViewAndCompare");
        } else if ( "forecastAnalysisView".equalsIgnoreCase(action) ) {
            String reporttype = request.getParameter("reporttype");
            String reportname = request.getParameter("reportname");

            if("snapshot".equalsIgnoreCase(reporttype)) {
                LinkedList forecastDataList = ForecastReportsDAO.getSalesForecastSnapshotReportRowDataList(reportname);
                Map monthsHeaderMap = (Map)forecastDataList.get(0);
                forecastDataList.remove(0);

                workbook = generateSnapshotWorkBook(forecastDataList, monthsHeaderMap, reportname);
                fileName = "Forecast Snapshot Report-"+reportname+"_"+ DateUtil.getTimeStamp() ;
                ExcelUtils.generateExcelReport( workbook, fileName, response );
            } else { //analysis
                String paramsStr = ForecastReportsDAO.getAnalysisParamStr(null, reportname);
                LinkedList forecastDataList = ForecastReportsDAO.getSalesForecastReportRowDataListForAnalysis();
                String[] paramstr = paramsStr.split("\\#");
                for(String params: paramstr) {
                    applyAnalysis( forecastDataList, params );
                }
                workbook =  generateWorkBook( forecastDataList );
                fileName = "Forecast Analysis Report_"+ DateUtil.getTimeStamp() ;
                ExcelUtils.generateExcelReport( workbook, fileName, response );
            }
            return null;
        } else if ( "compareForecastAnalysis".equalsIgnoreCase(action) ) {
            String reporttype1 = request.getParameter("reporttype1");
            String reportname1 = request.getParameter("reportname1");

            String reporttype2 = request.getParameter("reporttype2");
            String reportname2 = request.getParameter("reportname2");

            LinkedList report1forecastDataList = null;
            LinkedList report2forecastDataList = null;

            if("snapshot".equalsIgnoreCase(reporttype1)) {
                report1forecastDataList = ForecastReportsDAO.getSalesForecastSnapshotReportRowDataList(reportname1);
                Map monthsHeaderMap = (Map)report1forecastDataList.get(0);
                report1forecastDataList.remove(0);
            } else {
                String paramsStr = ForecastReportsDAO.getAnalysisParamStr(null, reportname1);
                report1forecastDataList = ForecastReportsDAO.getSalesForecastReportRowDataListForAnalysis();
                String[] paramstr = paramsStr.split("\\#");
                for(String params: paramstr) {
                    applyAnalysis( report1forecastDataList, params );
                }
            }

            if("snapshot".equalsIgnoreCase(reporttype2)) {
                report2forecastDataList = ForecastReportsDAO.getSalesForecastSnapshotReportRowDataList(reportname2);
                Map monthsHeaderMap = (Map)report2forecastDataList.get(0);
                report2forecastDataList.remove(0);
            } else {
                String paramsStr = ForecastReportsDAO.getAnalysisParamStr(null, reportname2);
                report2forecastDataList = ForecastReportsDAO.getSalesForecastReportRowDataListForAnalysis();
                String[] paramstr = paramsStr.split("\\#");
                for(String params: paramstr) {
                    applyAnalysis( report2forecastDataList, params );
                }
            }

            mergeReportsToCompare(report1forecastDataList, report2forecastDataList);
            workbook =  generateCompareWorkBook(report1forecastDataList, reportname1, reportname2);
            fileName = "Forecast Compare Report_"+ DateUtil.getTimeStamp() ;
            ExcelUtils.generateExcelReport( workbook, fileName, response );

            return null;
        }
        else
        {
            try
            {
                // Checking whether super user or not
                if ( pUser.isActionAllowed( Functionality.FORECAST_ENTRY_FOR_ALL_SALES_PERSONS, User.USER_ACTION_MODIFY ) )
                {
                    request.setAttribute("showList", "true");
                    request.setAttribute( "salesRepList", SalesPersonSetupDAO.getSalesPersonList() );
                }
                else // This block is for other than super user
                {
                    String salesPersonId = ( String ) session.getAttribute( USER_SALESPERSON_ID );
                    // Checking whether sales person or not
                    if /*( !pUser.isActionAllowed( Functionality.FORECAST_ENTRY_FOR_ALL_SALES_PERSONS, User.USER_ACTION_MODIFY ) &&*/ ( salesPersonId == null || salesPersonId.trim().length() == 0 ) //)
                    {
                        /*errors.put ( "You don't have permission to generate sales forecast report", null );
                        request.setAttribute( SessionKeys.ERROR_MESSAGES, errors );
                        return mapping.findForward ( "blankPage" );*/
                        request.setAttribute("showList", "true");
                        request.setAttribute( "salesRepList", SalesPersonSetupDAO.getSalesPersonList() );
                    }
                    else // for sales person
                    {
                        request.setAttribute("showList", "false");
                        request.setAttribute( "salesRepList", ForecastReportsDAO.getSalesPersonsWithManagerList(spersonId) );
                    }
                }
                salesForecastReportForm.setShowNext(6);
                //request.setAttribute( "salesRepList", NewSalesOrderDAO.loadSalesPersons() );
                //request.setAttribute( "endUserList", NewCustomerDAO.loadOdms() );
                Map userDefinedLabels = ForecastReportsDAO.getProjectUserDefinedFieldLables(null);
                salesForecastReportForm.setUserList1Label( (String)userDefinedLabels.get( "USERLIST1" ) );
                ArrayList userList1Values = UserDefinedFieldsDAO.loadUserDefinedListValues( null, 0, "PROJSETUP", (String)userDefinedLabels.get("USERLIST1") );
                request.setAttribute ( "userList1Values", userList1Values );
                request.setAttribute( "endUserList", ForecastReportsDAO.loadOdmList() );
                request.setAttribute( "quarterList", loadQuarters() );
                request.setAttribute( "currentQtr", getCurrentQuarter() );
                request.setAttribute( "monthsList", SalesForecastEntryDAO.getMonthsList() );
                request.setAttribute( "yearsList", SalesForecastEntryDAO.getYearList() );
                //request.setAttribute( "endUserList",  ForecastReportsDAO.getAllCustomers( null ) );
                request.setAttribute( "customersList",  ForecastReportsDAO.getAllCustomers( null ) );
                request.setAttribute( "productsList", ForecastReportsDAO.getAllProducts( null ) );

        }
        catch(Exception e)
        {
          e.printStackTrace();
        }

        if( action.equalsIgnoreCase("forecastActual") )
        {
            return( mapping.findForward( "forecastActualReport" ) );
        }
        if( action.equalsIgnoreCase("forecastReports") )
        {
            return( mapping.findForward( "salesForecastReport" ) );
        }
        }
        return null;
    }

    private Workbook generateWorkBook( LinkedList forecastDataList)
    {
		Workbook workbook   = null;
		Sheet sheet       = null;
		if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
        {
            workbook    = new XSSFWorkbook();
        }
        else
        {
            workbook    = new HSSFWorkbook();
        }
        CreationHelper ch = workbook.getCreationHelper();
        DataFormat df = workbook.createDataFormat();

		sheet       = workbook.createSheet( "Forecast Analysis Report" );
		sheet.setSelected( true );

		Font titleFont = workbook.createFont();
		titleFont.setFontHeightInPoints ( ( short ) 10 );
		titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
		titleFont.setFontName( "verdana" );
		titleFont.setColor ( IndexedColors.BLACK.getIndex());
		
		Font headerFont = workbook.createFont();
		headerFont.setFontHeightInPoints ( ( short ) 8 );
		headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
		headerFont.setFontName( "verdana" );
		headerFont.setColor ( IndexedColors.BLACK.getIndex());
		
		Font textFont = workbook.createFont();
		textFont.setFontHeightInPoints ( ( short ) 8 );
		textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
		textFont.setFontName( "verdana" );
		textFont.setColor ( IndexedColors.BLACK.getIndex());
		
		CellStyle titleStyle  = workbook.createCellStyle ( );
		titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
		titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
		titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
		titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
		titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
		titleStyle.setWrapText ( true );
		titleStyle.setFont ( titleFont );
		
		CellStyle headerStyle  = workbook.createCellStyle ( );
		headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
		headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
		headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
		headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
		headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
		headerStyle.setWrapText ( true );
		headerStyle.setFont ( headerFont );
		
		CellStyle productValueStyle  = workbook.createCellStyle ( );
		productValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
		productValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
		productValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
		productValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
		productValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
		productValueStyle.setWrapText ( true );
		productValueStyle.setFont ( textFont );
		productValueStyle.setDataFormat( df.getFormat( "#,##0" ) );
		
		CellStyle productValueBoldStyle  = workbook.createCellStyle ( );
		productValueBoldStyle.setBorderBottom ( CellStyle.BORDER_THIN );
		productValueBoldStyle.setBorderTop ( CellStyle.BORDER_THIN );
		productValueBoldStyle.setBorderLeft ( CellStyle.BORDER_THIN );
		productValueBoldStyle.setBorderRight ( CellStyle.BORDER_THIN );
		productValueBoldStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
		productValueBoldStyle.setWrapText ( true );
		productValueBoldStyle.setFont ( headerFont );
		productValueBoldStyle.setDataFormat( df.getFormat( "#,##0" ) );
		
		CellStyle priceStyle  = workbook.createCellStyle ( );
		priceStyle.setBorderBottom ( CellStyle.BORDER_THIN );
		priceStyle.setBorderTop ( CellStyle.BORDER_THIN );
		priceStyle.setBorderLeft ( CellStyle.BORDER_THIN );
		priceStyle.setBorderRight ( CellStyle.BORDER_THIN );
		priceStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
		priceStyle.setWrapText ( true );
		priceStyle.setFont ( textFont );
		priceStyle.setDataFormat( df.getFormat( "($#,##0.00_);[Red]($#,##0.00)" ) );
		
		CellStyle priceBoldStyle  = workbook.createCellStyle ( );
		priceBoldStyle.setBorderBottom ( CellStyle.BORDER_THIN );
		priceBoldStyle.setBorderTop ( CellStyle.BORDER_THIN );
		priceBoldStyle.setBorderLeft ( CellStyle.BORDER_THIN );
		priceBoldStyle.setBorderRight ( CellStyle.BORDER_THIN );
		priceBoldStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
		priceBoldStyle.setWrapText ( true );
		priceBoldStyle.setFont ( headerFont );
		priceBoldStyle.setDataFormat( df.getFormat( "($#,##0.00_);[Red]($#,##0.00)" ) );
		
		CellStyle productStyle  = workbook.createCellStyle ( );
		productStyle.setBorderBottom ( CellStyle.BORDER_THIN );
		productStyle.setBorderTop ( CellStyle.BORDER_THIN );
		productStyle.setBorderLeft ( CellStyle.BORDER_THIN );
		productStyle.setBorderRight ( CellStyle.BORDER_THIN );
		productStyle.setAlignment ( CellStyle.ALIGN_LEFT );
		productStyle.setWrapText ( true );
		productStyle.setFont ( textFont );
		
		//SETTING HEADINGS
		String headers[] = null;
		String subheaders[] = null;
		headers = new String[]{ "Customer",  "Sales Person", "Project", "Part Number" };
		subheaders = new String[]{ "Qty",  "ASP", "Rev", "Orig Qty",  "Orig ASP", "Orig Rev"  };
		int showNext = 12;

		// First Row
		Row row = sheet.createRow( 0 );
		Cell cell = mergeCells ( sheet, 0, 0, 0, ( (headers.length-1) + (2*showNext ) + 1 ), titleStyle );
		setCellValue ( cell, ch.createRichTextString("Forecast Analysis Report"), titleStyle );
		
		// Header Row
		Map<String, String> monthsHeaderMap = getMonthsHeaderAnalysisFormat ( DateUtil.getCurrentYear(), DateUtil.getCurrentMonth(), showNext );
		row = sheet.createRow ( 1 );
		cell    = mergeCells ( sheet, 1, 0, 1, (headers.length-1), titleStyle );
		setCellValue ( cell, ch.createRichTextString(""), headerStyle );
		int colNum = headers.length;
		Collection<String> mthHeaders = monthsHeaderMap.values();
		Iterator<String> monthIterator = mthHeaders.iterator();
		
		while ( monthIterator.hasNext() )
		{
			String monthItrValue = (String)monthIterator.next();
			if ( monthItrValue != null && monthItrValue.indexOf("Price") < 0 &&  monthItrValue.indexOf("Revenue") < 0 )
			{
				int endCell = colNum + 2;
				cell    = mergeCells ( sheet, 1, colNum, 1, endCell, titleStyle );
				setCellValue ( cell, ch.createRichTextString(monthItrValue), headerStyle );
				int width = ( monthItrValue.length() > 12 ) ? monthItrValue.length() : 12;
				sheet.setColumnWidth( colNum, ( ( ( width + 2 ) * 256 ) ) );
				colNum= colNum + 3;
			}    
		}
		row = sheet.createRow ( 2 );

		for ( int i = 0; i < headers.length; i++ )
		{
			cell    = row.createCell( i );
			setCellValue ( cell, ch.createRichTextString(headers[i]), headerStyle );
			int width = ( headers[i].length() > 12 ) ? headers[i].length() : 12;
			sheet.setColumnWidth( i, ( ( ( width + 2 ) * 256 ) ) );
		}
		int row2colNum =  headers.length;
		for ( int i= 0; i < showNext; i++ )
		{
			for ( int j=0; j < subheaders.length; j++ )
			{
				cell    = row.createCell( row2colNum );
				setCellValue ( cell, ch.createRichTextString(subheaders[j]), headerStyle );
				int width = ( subheaders[j].length() > 12 ) ? subheaders[j].length() : 12;
				sheet.setColumnWidth( row2colNum, ( ( ( width + 2 ) * 256 ) ) );
				row2colNum ++;
			}
		}
		
		int nextRowNumber = 3;
		int nextColumnIdx = 0;
		for ( int i=0; i < forecastDataList.size(); i++ )
		{
			RowData rowData = (RowData) forecastDataList.get(i);
			row = sheet.createRow( nextRowNumber++ );
			nextColumnIdx = 0;
			setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getCustomerName()), productStyle  );
			setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getSalesPersonName()), productStyle  );
			setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getProjName()), productStyle  );
			setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getProductName()), productStyle  );

			Iterator<String> monthKeyItr = monthsHeaderMap.keySet().iterator();
			while ( monthKeyItr.hasNext() )
			{
				String itrValue = (String)monthKeyItr.next();
				//	System.out.println("itrValue = " + itrValue);
				if ( itrValue.startsWith( "Qty" ) )
				{
					String[] split = itrValue.split("\\;");
					int month = Integer.parseInt( split[1] );
					int year = Integer.parseInt( split[2] );
					SlotData slotData = rowData.getSlotData( month, year );
					if ( slotData != null )
						setCellValue( row, nextColumnIdx++, slotData.getAcQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
					else
						setCellValue( row, nextColumnIdx++, ch.createRichTextString("0"), productValueStyle  );
				}
				else if ( itrValue.startsWith( "Price" ) )
				{
					String[] split = itrValue.split("\\;");
					int month = Integer.parseInt( split[1] );
					int year = Integer.parseInt( split[2] );
					SlotData slotData = rowData.getSlotData( month, year );
					if ( slotData != null  )
						setCellValue( row, nextColumnIdx++, slotData.getAcPrice(), priceStyle, Cell.CELL_TYPE_NUMERIC  );
					else
						setCellValue( row, nextColumnIdx++, ch.createRichTextString("0.0"), priceStyle  );
				}
				else if ( itrValue.startsWith( "Revenue" ) )
				{
					int priceIdx = (nextColumnIdx-1);
					int fromMthIdx = (nextColumnIdx-2);

					String formula =  getCellReference(fromMthIdx, nextRowNumber) + "*" + getCellReference(priceIdx, nextRowNumber);
					setCellFormula( row, nextColumnIdx++, formula, priceStyle  );
				}
				else if ( itrValue.startsWith( "Orig Qty" ) )
				{
					String[] split = itrValue.split("\\;");
					int month = Integer.parseInt( split[1] );
					int year = Integer.parseInt( split[2] );
					SlotData slotData = rowData.getSlotData( month, year );
					if ( slotData != null )
						setCellValue( row, nextColumnIdx++, slotData.getQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
					else
						setCellValue( row, nextColumnIdx++, ch.createRichTextString("0"), productValueStyle  );
				}
				else if ( itrValue.startsWith( "Orig Price" ) )
				{
					String[] split = itrValue.split("\\;");
					int month = Integer.parseInt( split[1] );
					int year = Integer.parseInt( split[2] );
					SlotData slotData = rowData.getSlotData( month, year );
					if ( slotData != null  )
						setCellValue( row, nextColumnIdx++, slotData.getPrice(), priceStyle, Cell.CELL_TYPE_NUMERIC  );
					else
						setCellValue( row, nextColumnIdx++, ch.createRichTextString("0.0"), priceStyle  );
				}
				else if ( itrValue.startsWith( "Orig Revenue" ) )
				{
					int priceIdx = (nextColumnIdx-1);
					int fromMthIdx = (nextColumnIdx-2);

					String formula =  getCellReference(fromMthIdx, nextRowNumber) + "*" + getCellReference(priceIdx, nextRowNumber);
					setCellFormula( row, nextColumnIdx++, formula, priceStyle  );
				}
			}
		}
		//--TOTAL ROW
		if ( nextRowNumber != 2 ) //data available
		{
			row = sheet.createRow( nextRowNumber );
			cell = mergeCells ( sheet, nextRowNumber, 0, nextRowNumber, (headers.length-1), titleStyle );
			setCellValue ( cell, ch.createRichTextString("Total"), titleStyle );
			Iterator<String> monthKeyItr = monthsHeaderMap.keySet().iterator();
			nextColumnIdx = headers.length;
			while ( monthKeyItr.hasNext() )
			{
				String itrValue = (String)monthKeyItr.next();
				if ( itrValue.startsWith( "Qty" ) )
				{
					String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
					setCellFormula( row, nextColumnIdx, totalFormula, productValueBoldStyle );
				}
				else if ( itrValue.startsWith( "Revenue" ) )
				{
					String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
					setCellFormula( row, nextColumnIdx, totalFormula, priceBoldStyle );
				}
				if ( itrValue.startsWith( "Orig Qty" ) )
				{
					String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
					setCellFormula( row, nextColumnIdx, totalFormula, productValueBoldStyle );
				}
				else if ( itrValue.startsWith( "Orig Revenue" ) )
				{
					String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
					setCellFormula( row, nextColumnIdx, totalFormula, priceBoldStyle );
				}

				nextColumnIdx++;
			}
		}

		return workbook;
	}

	private void applyAnalysis( LinkedList<RowData> forecastDataList, String paramsStr )
    {
        SalesForecastReportForm salesForecastReportForm = getFilledForecastReportForm(paramsStr);
        int priceChange = salesForecastReportForm.getPrice();
    	int qtyChange   = salesForecastReportForm.getQty();
    	AutoGrowingLinkedList slotDataList = null;
    	SlotData slotData = null;
    	String priceType 	= salesForecastReportForm.getPriceType();
    	String qtyType 	= salesForecastReportForm.getQtyType();

        //Apply Calculations to the Forecast Data
        for( RowData rowData:forecastDataList )
		{
            //Apply calculations to RowData
            if ( isRowDataInSearchCriteria(rowData, salesForecastReportForm)){
                rowData.setSelected(true);
                slotDataList = rowData.getSlotDataList();
                Iterator<SlotData> slotIterator = slotDataList.iterator();
                for( ;slotIterator.hasNext(); )
                {
                    slotData = slotIterator.next();
                    if(isSlotDataInSearchCriteria(slotData, salesForecastReportForm)) {
                        slotData.setAcPrice( getCalPrice( slotData.getAcPrice(), priceType, priceChange ) );
                        slotData.setAcQty( getCalQty( slotData.getAcQty(), qtyType, qtyChange ) );
                    }
                }
            }
        }

	}

	private int getCalQty(int qty, String qtyType, int qtyChange) 
	{
		double qtydb = Double.parseDouble(Integer.toString(qty));
		if( qty != 0 && qtyChange != 0)
		{
			if( qtyType.equalsIgnoreCase( "0" ) )
			{
				qtydb = qtydb + (qtydb * (Double.parseDouble(Integer.toString(qtyChange))/100));
			}
			else
			{
				qtydb = qtydb - (qtydb * (Double.parseDouble(Integer.toString(qtyChange))/100));
			}
			
		}
		return new Double( qtydb ).intValue();
	}

	private double getCalPrice(double price, String priceType, int priceChange) 
	{
		if( price != 0.0 && priceChange != 0.0 )
		{
			if( priceType.equalsIgnoreCase( "0" ) )
			{
				price = price + (price * (Double.parseDouble(Integer.toString(priceChange))/100.00));
			}
			else
			{
				price = price - (price * (Double.parseDouble(Integer.toString(priceChange))/100.00));
			}
		}

		return price;
	}

	public ArrayList loadQuarterList()
    {
        int start_qtr = 1;
        int end_qtr = 4;

        ArrayList<LabelValueBean> yearList = new ArrayList<LabelValueBean> ();
        for ( int year = start_qtr; year <= end_qtr; year++ )
        {
            yearList.add ( new LabelValueBean ( "Qtr " + year, year + "") );
        }
        yearList.add( new LabelValueBean ( "All", "All") );

        return yearList;
    }

    public ArrayList<LabelValueBean> loadQuarters()
    {
        int start_qtr = 1;
        int end_qtr = 4;

        ArrayList<LabelValueBean> qtrList = new ArrayList<LabelValueBean> ();
        for ( int qtr = start_qtr; qtr <= end_qtr; qtr++ )
        {
            qtrList.add ( new LabelValueBean ( "Qtr " + qtr, qtr + "") );
        }
        //yearList.add( new LabelValueBean ( "All", "All") );

        return qtrList;
    }

    private String getCurrentQuarter()
    {
        String qtr = "1";
        Calendar c = Calendar.getInstance();
        int month = c.get( Calendar.MONTH );

        if( month > 8 )
        {
            qtr = "4";
        }
        else if( month > 5 )
        {
            qtr = "3";
        }
        else if( month > 2 )
        {
            qtr = "2";
        }
        else
        {
            qtr = "1";
        }
        return qtr;
    }
    private Workbook getForecastReportWorkBook ( SalesForecastReportForm salesForecastReportForm ) throws Exception
    {
        LinkedList forecastDataList = ForecastReportsDAO.getSalesForecastReportRowDataList( null, salesForecastReportForm.getCustomerId(), salesForecastReportForm.getEndCustomerId(),
                                                            salesForecastReportForm.getProductId(), salesForecastReportForm.getSalesPersonId(),
                                                            salesForecastReportForm.getConfidenceLevel(), salesForecastReportForm.getPeriod(), salesForecastReportForm.getQtrNo(),
                                                            salesForecastReportForm.getYear(), salesForecastReportForm.getFromMonth(), salesForecastReportForm.getShowNext(),
                                                            salesForecastReportForm.getCategory(), salesForecastReportForm.getReportType(), salesForecastReportForm.getUserList1Val() );
        Map userDefinedLabels = ForecastReportsDAO.getProjectUserDefinedFieldLables(null);
        String userList1Label = (String)userDefinedLabels.get( "USERLIST1" );
        String userList2Label = (String)userDefinedLabels.get( "USERLIST2" );
        String userTxt1Label = (String)userDefinedLabels.get( "USERTXT1" );
        String endUserLabel = MessageResources.getMessageResources ( Labels.APPLICATION_RESOURCE_PROPERTIES ).getMessage ( "label.sales.endUser" );

        Workbook workbook   = null;
        Sheet sheet       = null;
        if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
        {
            workbook    = new XSSFWorkbook();
        }
        else
        {
            workbook    = new HSSFWorkbook();
        }
        CreationHelper ch = workbook.getCreationHelper();
        DataFormat df = workbook.createDataFormat();

        sheet       = workbook.createSheet( "Forecast Report" );
        sheet.setSelected( true );

        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints ( ( short ) 10 );
        titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        titleFont.setFontName( "verdana" );
        titleFont.setColor ( IndexedColors.BLACK.getIndex());

        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints ( ( short ) 8 );
        headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        headerFont.setFontName( "verdana" );
        headerFont.setColor ( IndexedColors.BLACK.getIndex());

        Font textFont = workbook.createFont();
        textFont.setFontHeightInPoints ( ( short ) 8 );
        textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
        textFont.setFontName( "verdana" );
        textFont.setColor ( IndexedColors.BLACK.getIndex());

        CellStyle titleStyle  = workbook.createCellStyle ( );
        titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
        titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
        titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        titleStyle.setWrapText ( true );
        titleStyle.setFont ( titleFont );

        CellStyle headerStyle  = workbook.createCellStyle ( );
        headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
        headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
        headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        headerStyle.setWrapText ( true );
        headerStyle.setFont ( headerFont );

        CellStyle productValueStyle  = workbook.createCellStyle ( );
        productValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        productValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
        productValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        productValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
        productValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        productValueStyle.setWrapText ( true );
        productValueStyle.setFont ( textFont );
        productValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

        CellStyle productValueBoldStyle  = workbook.createCellStyle ( );
        productValueBoldStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        productValueBoldStyle.setBorderTop ( CellStyle.BORDER_THIN );
        productValueBoldStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        productValueBoldStyle.setBorderRight ( CellStyle.BORDER_THIN );
        productValueBoldStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        productValueBoldStyle.setWrapText ( true );
        productValueBoldStyle.setFont ( headerFont );
        productValueBoldStyle.setDataFormat( df.getFormat( "#,##0" ) );

        CellStyle priceStyle  = workbook.createCellStyle ( );
        priceStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        priceStyle.setBorderTop ( CellStyle.BORDER_THIN );
        priceStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        priceStyle.setBorderRight ( CellStyle.BORDER_THIN );
        priceStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        priceStyle.setWrapText ( true );
        priceStyle.setFont ( textFont );
        priceStyle.setDataFormat( df.getFormat( "($#,##0.00_);[Red]($#,##0.00)" ) );

        CellStyle priceBoldStyle  = workbook.createCellStyle ( );
        priceBoldStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        priceBoldStyle.setBorderTop ( CellStyle.BORDER_THIN );
        priceBoldStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        priceBoldStyle.setBorderRight ( CellStyle.BORDER_THIN );
        priceBoldStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        priceBoldStyle.setWrapText ( true );
        priceBoldStyle.setFont ( headerFont );
        priceBoldStyle.setDataFormat( df.getFormat( "($#,##0.00_);[Red]($#,##0.00)" ) );

        CellStyle productStyle  = workbook.createCellStyle ( );
        productStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        productStyle.setBorderTop ( CellStyle.BORDER_THIN );
        productStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        productStyle.setBorderRight ( CellStyle.BORDER_THIN );
        productStyle.setAlignment ( CellStyle.ALIGN_LEFT );
        productStyle.setWrapText ( true );
        productStyle.setFont ( textFont );

        //SETTING HEADINGS
        ArrayList<String> headersList = new ArrayList<String>();
        headersList.add("Customer");
        if ( ConfigManager.getBooleanProperty(ConfigManager.PROP_SHOW_END_USER_IN_FORECAST_REPORTS) ) {
            headersList.add(endUserLabel);
        }
        headersList.add("Sales Person");
        headersList.add("Project");
        if ( ConfigManager.getBooleanProperty(ConfigManager.PROP_SHOW_USERTXT1_IN_FORECAST_REPORTS) ) {
            headersList.add(userTxt1Label);
        }
        headersList.add("Part Number");
        if ( userList1Label != null && userList1Label.trim().length() > 0 ) {
            headersList.add(userList1Label);
        }


        Row row = sheet.createRow( 0 );
        Cell cell = mergeCells ( sheet, 0, 0, 0, ( (headersList.size()-1) + (2*salesForecastReportForm.getShowNext() ) + 1 ), titleStyle );
        setCellValue ( cell, ch.createRichTextString("Forecast Report"), titleStyle );
        Map monthsHeaderMap = getMonthsHeaderMap ( salesForecastReportForm.getPeriod(), salesForecastReportForm.getYear(), salesForecastReportForm.getQtrNo(), salesForecastReportForm.getFromMonth(), salesForecastReportForm.getShowNext() );
        row = sheet.createRow ( 1 );
        for ( int i = 0; i < headersList.size() ; i++ )
        {
            cell    = row.createCell( i );
            setCellValue ( cell, ch.createRichTextString(headersList.get(i)), headerStyle );
            int width = ( headersList.get(i).length() > 12 ) ? headersList.get(i).length() : 12;
            sheet.setColumnWidth( i, ( ( ( width + 2 ) * 256 ) ) );
        }
        int colNum = headersList.size();
        Collection mthHeaders = monthsHeaderMap.values();
        Iterator monthIterator = mthHeaders.iterator();
        while ( monthIterator.hasNext() )
        {
            String monthItrValue = (String)monthIterator.next();
            cell    = row.createCell( colNum );
            setCellValue ( cell, ch.createRichTextString(monthItrValue), headerStyle );
            int width = ( monthItrValue.length() > 12 ) ? monthItrValue.length() : 12;
            sheet.setColumnWidth( colNum, ( ( ( width + 2 ) * 256 ) ) );
            colNum++;
        }
        int nextRowNumber = 2;
        int nextColumnIdx = 0;
        for ( int i=0; i < forecastDataList.size(); i++ )
        {
            RowData rowData = (RowData) forecastDataList.get(i);
            row = sheet.createRow( nextRowNumber++ );
            nextColumnIdx = 0;
            setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getCustomerName()), productStyle  );
            if ( ConfigManager.getBooleanProperty(ConfigManager.PROP_SHOW_END_USER_IN_FORECAST_REPORTS) ) {
                setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getEndUserName()), productStyle  );
            }
            setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getSalesPersonName()), productStyle  );
            setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getProjName()), productStyle  );
            if ( ConfigManager.getBooleanProperty(ConfigManager.PROP_SHOW_USERTXT1_IN_FORECAST_REPORTS) ) {
                setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getUserDefined1()), productStyle  );
            }
            setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getProductName()), productStyle  );
            if ( userList1Label != null && userList1Label.trim().length() > 0 )
            {
                setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getUserList1Val()), productStyle  );
            }
            Iterator monthKeyItr = monthsHeaderMap.keySet().iterator();
            while ( monthKeyItr.hasNext() )
            {
                String itrValue = (String)monthKeyItr.next();
                if ( itrValue.startsWith( "Price" ) )
                {
                    String[] split = itrValue.split("\\;");
                    int month = Integer.parseInt( split[1] );
                    int year = Integer.parseInt( split[2] );
                    SlotData slotData = rowData.getSlotData( month, year );
                    if ( slotData != null  )
                        setCellValue( row, nextColumnIdx++, slotData.getPrice(), priceStyle, Cell.CELL_TYPE_NUMERIC  );
                    else
                        setCellValue( row, nextColumnIdx++, null, priceStyle  );
                }
                else if ( itrValue.startsWith( "Qty" ) )
                {
                    String[] split = itrValue.split("\\;");
                    int month = Integer.parseInt( split[1] );
                    int year = Integer.parseInt( split[2] );
                    SlotData slotData = rowData.getSlotData( month, year );
                    if ( slotData != null )
                        setCellValue( row, nextColumnIdx++, slotData.getQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    else
                        setCellValue( row, nextColumnIdx++, null, productValueStyle  );
                }
                else if ( itrValue.startsWith( "Revenue" ) )
                {
                    String[] split = itrValue.split("\\;");
                    int priceIdx = Integer.parseInt( split[1] ) + headersList.size();
                    int fromMthIdx = Integer.parseInt( split[2] ) + headersList.size();
                    int toMthIdx = Integer.parseInt( split[3] ) + headersList.size();
                    String formula = "SUM(" + getCellReference(fromMthIdx, nextRowNumber) + ":" + getCellReference(toMthIdx, nextRowNumber) + ")*" + getCellReference(priceIdx, nextRowNumber);
                    setCellFormula( row, nextColumnIdx++, formula, priceStyle  );
                }
            }
        }
        //--TOTAL ROW
        if ( nextRowNumber != 2 ) //data available
        {
            row = sheet.createRow( nextRowNumber );
            cell = mergeCells ( sheet, nextRowNumber, 0, nextRowNumber, (headersList.size()-1), titleStyle );
            setCellValue ( cell, ch.createRichTextString("Total"), titleStyle );
            Iterator monthKeyItr = monthsHeaderMap.keySet().iterator();
            nextColumnIdx = headersList.size();
            while ( monthKeyItr.hasNext() )
            {
                String itrValue = (String)monthKeyItr.next();
                if ( itrValue.startsWith( "Qty" ) )
                {
                    String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
                    setCellFormula( row, nextColumnIdx, totalFormula, productValueBoldStyle );
                }
                else if ( itrValue.startsWith( "Revenue" ) )
                {
                    String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
                    setCellFormula( row, nextColumnIdx, totalFormula, priceBoldStyle );
                }
                else
                {
                    setCellValue( row, nextColumnIdx, null, priceBoldStyle  );
                }
                nextColumnIdx++;
            }
        }

        return workbook;
    }
    private Workbook getForecastReportWorkBookNewFormat( SalesForecastReportForm salesForecastReportForm ) throws Exception
    {
        LinkedList forecastDataList = ForecastReportsDAO.getSalesForecastReportRowDataList( null, salesForecastReportForm.getCustomerId(), salesForecastReportForm.getEndCustomerId(),
                                                            salesForecastReportForm.getProductId(), salesForecastReportForm.getSalesPersonId(),
                                                            salesForecastReportForm.getConfidenceLevel(), salesForecastReportForm.getPeriod(), salesForecastReportForm.getQtrNo(),
                                                            salesForecastReportForm.getYear(), salesForecastReportForm.getFromMonth(), salesForecastReportForm.getShowNext(),
                                                            salesForecastReportForm.getCategory(), salesForecastReportForm.getReportType(), salesForecastReportForm.getUserList1Val() );
        Map userDefinedLabels = ForecastReportsDAO.getProjectUserDefinedFieldLables(null);
        String userList1Label = (String)userDefinedLabels.get( "USERLIST1" );
        String userList2Label = (String)userDefinedLabels.get( "USERLIST2" );

        Workbook workbook   = null;
        Sheet sheet       = null;
        if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
        {
            workbook    = new XSSFWorkbook();
        }
        else
        {
            workbook    = new HSSFWorkbook();
        }
        CreationHelper ch = workbook.getCreationHelper();
        DataFormat df = workbook.createDataFormat();

        sheet       = workbook.createSheet( "Forecast Report" );
        sheet.setSelected( true );

        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints ( ( short ) 10 );
        titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        titleFont.setFontName( "verdana" );
        titleFont.setColor ( IndexedColors.BLACK.getIndex());

        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints ( ( short ) 8 );
        headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        headerFont.setFontName( "verdana" );
        headerFont.setColor ( IndexedColors.BLACK.getIndex());

        Font textFont = workbook.createFont();
        textFont.setFontHeightInPoints ( ( short ) 8 );
        textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
        textFont.setFontName( "verdana" );
        textFont.setColor ( IndexedColors.BLACK.getIndex());

        CellStyle titleStyle  = workbook.createCellStyle ( );
        titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
        titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
        titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        titleStyle.setWrapText ( true );
        titleStyle.setFont ( titleFont );

        CellStyle headerStyle  = workbook.createCellStyle ( );
        headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
        headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
        headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        headerStyle.setWrapText ( true );
        headerStyle.setFont ( headerFont );

        CellStyle productValueStyle  = workbook.createCellStyle ( );
        productValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        productValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
        productValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        productValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
        productValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        productValueStyle.setWrapText ( true );
        productValueStyle.setFont ( textFont );
        productValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

        CellStyle productValueBoldStyle  = workbook.createCellStyle ( );
        productValueBoldStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        productValueBoldStyle.setBorderTop ( CellStyle.BORDER_THIN );
        productValueBoldStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        productValueBoldStyle.setBorderRight ( CellStyle.BORDER_THIN );
        productValueBoldStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        productValueBoldStyle.setWrapText ( true );
        productValueBoldStyle.setFont ( headerFont );
        productValueBoldStyle.setDataFormat( df.getFormat( "#,##0" ) );

        CellStyle priceStyle  = workbook.createCellStyle ( );
        priceStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        priceStyle.setBorderTop ( CellStyle.BORDER_THIN );
        priceStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        priceStyle.setBorderRight ( CellStyle.BORDER_THIN );
        priceStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        priceStyle.setWrapText ( true );
        priceStyle.setFont ( textFont );
        priceStyle.setDataFormat( df.getFormat( "($#,##0.00_);[Red]($#,##0.00)" ) );

        CellStyle priceBoldStyle  = workbook.createCellStyle ( );
        priceBoldStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        priceBoldStyle.setBorderTop ( CellStyle.BORDER_THIN );
        priceBoldStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        priceBoldStyle.setBorderRight ( CellStyle.BORDER_THIN );
        priceBoldStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        priceBoldStyle.setWrapText ( true );
        priceBoldStyle.setFont ( headerFont );
        priceBoldStyle.setDataFormat( df.getFormat( "($#,##0.00_);[Red]($#,##0.00)" ) );

        CellStyle productStyle  = workbook.createCellStyle ( );
        productStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        productStyle.setBorderTop ( CellStyle.BORDER_THIN );
        productStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        productStyle.setBorderRight ( CellStyle.BORDER_THIN );
        productStyle.setAlignment ( CellStyle.ALIGN_LEFT );
        productStyle.setWrapText ( true );
        productStyle.setFont ( textFont );

        //SETTING HEADINGS
        String headers[] = null;
        String subheaders[] = null;
        if ( userList1Label != null && userList1Label.trim().length() > 0 )
        {
            headers = new String[]{ "Customer",  "Sales Person", "Project", "Part Number", userList1Label };
        }
        else
        {
            headers = new String[]{ "Customer",  "Sales Person", "Project", "Part Number" };
        }
        subheaders = new String[]{ "Qty",  "ASP", "Rev" };
        int showNext = 0;
        if ("byQuarter".equalsIgnoreCase( salesForecastReportForm.getPeriod() ) )
        {
            showNext = 3;
        }
        else
        {
            showNext = salesForecastReportForm.getShowNext();
        }

        Row row = sheet.createRow( 0 );
        Cell cell = mergeCells ( sheet, 0, 0, 0, ( (headers.length-1) + (2*showNext ) + 1 ), titleStyle );
        setCellValue ( cell, ch.createRichTextString("Forecast Report"), titleStyle );
        Map monthsHeaderMap = getMonthsHeaderMapNewFormat ( salesForecastReportForm.getPeriod(), salesForecastReportForm.getYear(), salesForecastReportForm.getQtrNo(), salesForecastReportForm.getFromMonth(), salesForecastReportForm.getShowNext() );
        row = sheet.createRow ( 1 );
        cell    = mergeCells ( sheet, 1, 0, 1, (headers.length-1), titleStyle );
        setCellValue ( cell, ch.createRichTextString(""), headerStyle );
        int colNum = headers.length;
        Collection mthHeaders = monthsHeaderMap.values();
        Iterator monthIterator = mthHeaders.iterator();
        while ( monthIterator.hasNext() )
        {
            String monthItrValue = (String)monthIterator.next();
            if ( monthItrValue != null && monthItrValue.indexOf("Price") < 0 &&  monthItrValue.indexOf("Revenue") < 0 )
            {
                int endCell = colNum + 2;
                cell    = mergeCells ( sheet, 1, colNum, 1, endCell, titleStyle );
                setCellValue ( cell, ch.createRichTextString(monthItrValue), headerStyle );
                int width = ( monthItrValue.length() > 12 ) ? monthItrValue.length() : 12;
                sheet.setColumnWidth( colNum, ( ( ( width + 2 ) * 256 ) ) );
                colNum= colNum + 3;
            }    
        }
        row = sheet.createRow ( 2 );
        for ( int i = 0; i < headers.length; i++ )
        {
            cell    = row.createCell( i );
            setCellValue ( cell, ch.createRichTextString(headers[i]), headerStyle );
            int width = ( headers[i].length() > 12 ) ? headers[i].length() : 12;
            sheet.setColumnWidth( i, ( ( ( width + 2 ) * 256 ) ) );
        }
        int row2colNum =  headers.length;
        for ( int i= 0; i < showNext; i++ )
        {
            for ( int j=0; j < subheaders.length; j++ )
            {
               cell    = row.createCell( row2colNum );
               setCellValue ( cell, ch.createRichTextString(subheaders[j]), headerStyle );
               int width = ( subheaders[j].length() > 12 ) ? subheaders[j].length() : 12;
               sheet.setColumnWidth( row2colNum, ( ( ( width + 2 ) * 256 ) ) );
               row2colNum ++;
            }
        }
        int nextRowNumber = 3;
        int nextColumnIdx = 0;
        for ( int i=0; i < forecastDataList.size(); i++ )
        {
            RowData rowData = (RowData) forecastDataList.get(i);
            row = sheet.createRow( nextRowNumber++ );
            nextColumnIdx = 0;
            setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getCustomerName()), productStyle  );
            setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getSalesPersonName()), productStyle  );
            setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getProjName()), productStyle  );
            setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getProductName()), productStyle  );
            if ( userList1Label != null && userList1Label.trim().length() > 0 )
            {
                setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getUserList1Val()), productStyle  );
            }
            //System.out.println("nextColumnIdx = " + nextColumnIdx);
            Iterator monthKeyItr = monthsHeaderMap.keySet().iterator();
            while ( monthKeyItr.hasNext() )
            {
                String itrValue = (String)monthKeyItr.next();
                //System.out.println("itrValue = " + itrValue);
                if ( itrValue.startsWith( "Qty" ) )
                {
                    String[] split = itrValue.split("\\;");
                    int month = Integer.parseInt( split[1] );
                    int year = Integer.parseInt( split[2] );
                    SlotData slotData = rowData.getSlotData( month, year );
                    if ( slotData != null )
                        setCellValue( row, nextColumnIdx++, slotData.getQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                    else
                        setCellValue( row, nextColumnIdx++, ch.createRichTextString("0"), productValueStyle  );
                }
                else if ( itrValue.startsWith( "Price" ) )
                {
                    String[] split = itrValue.split("\\;");
                    int month = Integer.parseInt( split[1] );
                    int year = Integer.parseInt( split[2] );
                    SlotData slotData = rowData.getSlotData( month, year );
                    if ( slotData != null  )
                        setCellValue( row, nextColumnIdx++, slotData.getPrice(), priceStyle, Cell.CELL_TYPE_NUMERIC  );
                    else
                        setCellValue( row, nextColumnIdx++, ch.createRichTextString("0.0"), priceStyle  );
                }
                else if ( itrValue.startsWith( "Revenue" ) )
                {
                    int priceIdx = (nextColumnIdx-1);
                    int fromMthIdx = (nextColumnIdx-2);

                    String formula =  getCellReference(fromMthIdx, nextRowNumber) + "*" + getCellReference(priceIdx, nextRowNumber);
                    setCellFormula( row, nextColumnIdx++, formula, priceStyle  );
                }
            }
        }
        //--TOTAL ROW
        if ( nextRowNumber != 2 ) //data available
        {
            row = sheet.createRow( nextRowNumber );
            cell = mergeCells ( sheet, nextRowNumber, 0, nextRowNumber, (headers.length-1), titleStyle );
            setCellValue ( cell, ch.createRichTextString("Total"), titleStyle );
            Iterator monthKeyItr = monthsHeaderMap.keySet().iterator();
            nextColumnIdx = headers.length;
            while ( monthKeyItr.hasNext() )
            {
                String itrValue = (String)monthKeyItr.next();
                if ( itrValue.startsWith( "Qty" ) )
                {
                    String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
                    setCellFormula( row, nextColumnIdx, totalFormula, productValueBoldStyle );
                }
                else if ( itrValue.startsWith( "Revenue" ) )
                {
                    String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
                    setCellFormula( row, nextColumnIdx, totalFormula, priceBoldStyle );
                }
                else
                {
                    setCellValue( row, nextColumnIdx, null, priceBoldStyle  );
                }
                nextColumnIdx++;
            }
        }

        return workbook;
    }
    private Workbook getForecastBacklogReportWorkBook ( SalesForecastReportForm salesForecastReportForm ) throws Exception
    {
        LinkedList forecastDataList = ForecastReportsDAO.getSalesForecastBacklogReportRowDataList( null, salesForecastReportForm.getCustomerId(), salesForecastReportForm.getEndCustomerId(),
                                                            salesForecastReportForm.getProductId(), salesForecastReportForm.getSalesPersonId(),
                                                            salesForecastReportForm.getConfidenceLevel(), salesForecastReportForm.getPeriod(), salesForecastReportForm.getQtrNo(),
                                                            salesForecastReportForm.getYear(), salesForecastReportForm.getFromMonth(), salesForecastReportForm.getShowNext(),
                                                            salesForecastReportForm.getCategory(), salesForecastReportForm.getReportType() );
        Workbook workbook   = null;
        Sheet sheet       = null;
        if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
        {
            workbook    = new XSSFWorkbook();
        }
        else
        {
            workbook    = new HSSFWorkbook();
        }
        CreationHelper ch = workbook.getCreationHelper();
        DataFormat df = workbook.createDataFormat();
        sheet       = workbook.createSheet( "Forecast  Vs Backlog Report" );
        sheet.setSelected( true );

        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints ( ( short ) 10 );
        titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        titleFont.setFontName( "verdana" );
        titleFont.setColor ( IndexedColors.BLACK.getIndex());

        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints ( ( short ) 8 );
        headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        headerFont.setFontName( "verdana" );
        headerFont.setColor ( IndexedColors.BLACK.getIndex());

        Font textFont = workbook.createFont();
        textFont.setFontHeightInPoints ( ( short ) 8 );
        textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
        textFont.setFontName( "verdana" );
        textFont.setColor ( IndexedColors.BLACK.getIndex());

        CellStyle titleStyle  = workbook.createCellStyle ( );
        titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
        titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
        titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        titleStyle.setWrapText ( true );
        titleStyle.setFont ( titleFont );

        CellStyle headerStyle  = workbook.createCellStyle ( );
        headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
        headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
        headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        headerStyle.setWrapText ( true );
        headerStyle.setFont ( headerFont );

        CellStyle productValueStyle  = workbook.createCellStyle ( );
        productValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        productValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
        productValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        productValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
        productValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        productValueStyle.setWrapText ( true );
        productValueStyle.setFont ( textFont );
        productValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

        CellStyle productValueBoldStyle  = workbook.createCellStyle ( );
        productValueBoldStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        productValueBoldStyle.setBorderTop ( CellStyle.BORDER_THIN );
        productValueBoldStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        productValueBoldStyle.setBorderRight ( CellStyle.BORDER_THIN );
        productValueBoldStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        productValueBoldStyle.setWrapText ( true );
        productValueBoldStyle.setFont ( headerFont );
        productValueBoldStyle.setDataFormat( df.getFormat( "#,##0" ) );

        CellStyle priceStyle  = workbook.createCellStyle ( );
        priceStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        priceStyle.setBorderTop ( CellStyle.BORDER_THIN );
        priceStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        priceStyle.setBorderRight ( CellStyle.BORDER_THIN );
        priceStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        priceStyle.setWrapText ( true );
        priceStyle.setFont ( textFont );
        priceStyle.setDataFormat( df.getFormat( "($#,##0.00_);[Red]($#,##0.00)" ) );

        CellStyle priceBoldStyle  = workbook.createCellStyle ( );
        priceBoldStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        priceBoldStyle.setBorderTop ( CellStyle.BORDER_THIN );
        priceBoldStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        priceBoldStyle.setBorderRight ( CellStyle.BORDER_THIN );
        priceBoldStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        priceBoldStyle.setWrapText ( true );
        priceBoldStyle.setFont ( headerFont );
        priceBoldStyle.setDataFormat( df.getFormat( "($#,##0.00_);[Red]($#,##0.00)" ) );

        CellStyle productStyle  = workbook.createCellStyle ( );
        productStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        productStyle.setBorderTop ( CellStyle.BORDER_THIN );
        productStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        productStyle.setBorderRight ( CellStyle.BORDER_THIN );
        productStyle.setAlignment ( CellStyle.ALIGN_LEFT );
        productStyle.setWrapText ( true );
        productStyle.setFont ( textFont );

        //SETTING HEADINGS
        String category= salesForecastReportForm.getCategory();
        String firstCol= null;
        if ("byProduct".equalsIgnoreCase(category))
        {
            firstCol = "Product";
        }
        else if("byEndCust".equalsIgnoreCase(category))
        {
            firstCol = "End Custoemer";            
        }
        else if ( "bySalesPerson".equalsIgnoreCase(category))
        {
            firstCol = "Sales Person";
        }
        String headers[] = { firstCol,  "Month", "Year", "Forecast Qty", " Foreecast Revenue" ,"Backlog Qty", " Backlog Revenue" ,"Shipped Qty", " Shipped Revenue", "Backlog+Shipped Qty", "Backlog+Shipped Revenue" };
        Row row = sheet.createRow( 0 );
        Cell cell = mergeCells ( sheet, 0, 0, 0, (headers.length-1), titleStyle );
        setCellValue ( cell, ch.createRichTextString("Forecast Vs Backlog Report"), titleStyle );
        row = sheet.createRow ( 1 );
        for ( int i = 0; i < headers.length; i++ )
        {
            cell    = row.createCell( i );
            setCellValue ( cell, ch.createRichTextString(headers[i]), headerStyle );
            int width = ( headers[i].length() > 12 ) ? headers[i].length() : 12;
            sheet.setColumnWidth( i, ( ( ( width + 2 ) * 256 ) ) );
        }
        int colNum = headers.length;
        int nextRowNumber = 2;
        int nextColumnIdx = 0;
        int forecastQty =0;
        int backlogQty=0;
        int shipQty = 0;
        int backlogShippedQty =0 ;
        double forecastPrice =0;
        double backlogPrice=0;
        double shipPrice = 0;
        double backlogShippedPrice =0 ;
        for ( int i=0; i < forecastDataList.size(); i++ )
        {
            RowData rowData = (RowData) forecastDataList.get(i);
            row = sheet.createRow( nextRowNumber++ );
            String key = null;
            nextColumnIdx = 0;
            if ("byEndCust".equalsIgnoreCase(salesForecastReportForm.getCategory()))
            {
                setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getEndCustomerName()), productStyle  );
                key=rowData.getEndCustomerName();
            }
            else if("byProduct".equalsIgnoreCase(salesForecastReportForm.getCategory()))
            {
                setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getProductName()), productStyle  );
                key=rowData.getProductName();
            }
            else if ("bySalesPerson".equalsIgnoreCase(salesForecastReportForm.getCategory()))
            {
                setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getSalesPersonName()), productStyle  );
                key=rowData.getSalesPersonName();
            }
            key = key + "_" + rowData.getMonth() + "_" + rowData.getYear();
            SlotData slotData = rowData.getSlotData( key, rowData.getMonth(), rowData.getYear() );

            if ( slotData != null  )
            {
                setCellValue( row, nextColumnIdx++, ch.createRichTextString(DateUtil.getShortMonthName(slotData.getMonth())), productStyle  );
                setCellValue( row, nextColumnIdx++, ch.createRichTextString(slotData.getYear()+""), productStyle  );
                setCellValue( row, nextColumnIdx++, slotData.getQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                forecastQty = forecastQty + slotData.getQty();
                setCellValue( row, nextColumnIdx++, slotData.getPrice(), priceStyle, Cell.CELL_TYPE_NUMERIC  );
                forecastPrice = forecastPrice +slotData.getPrice();
                setCellValue( row, nextColumnIdx++, slotData.getBlQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                backlogQty = backlogQty + slotData.getBlQty();
                setCellValue( row, nextColumnIdx++, slotData.getBlPrice(), priceStyle, Cell.CELL_TYPE_NUMERIC  );
                backlogPrice = backlogPrice +slotData.getBlPrice();
                setCellValue( row, nextColumnIdx++, slotData.getAcQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                shipQty = shipQty + slotData.getAcQty();
                setCellValue( row, nextColumnIdx++, slotData.getAcPrice(), priceStyle, Cell.CELL_TYPE_NUMERIC  );
                shipPrice = shipPrice +slotData.getAcPrice();
                setCellValue( row, nextColumnIdx++, slotData.getAcQty()+slotData.getBlQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
                backlogShippedQty = backlogShippedQty + (slotData.getAcQty()+slotData.getBlQty());
                setCellValue( row, nextColumnIdx++, slotData.getAcPrice()+ slotData.getBlPrice(), priceStyle, Cell.CELL_TYPE_NUMERIC  );
                backlogShippedPrice = backlogShippedPrice + (slotData.getAcPrice()+ slotData.getBlPrice());
            }
        }
        //--TOTAL ROW
        if ( nextRowNumber != 2 ) //data available
        {
            row = sheet.createRow( nextRowNumber );
            cell = mergeCells ( sheet, nextRowNumber, 0, nextRowNumber, 2, titleStyle);
            setCellValue ( cell, ch.createRichTextString("Total"), titleStyle );
            nextColumnIdx = 3;
            setCellValue( row, nextColumnIdx++, forecastQty , productValueBoldStyle, Cell.CELL_TYPE_NUMERIC );
            setCellValue( row, nextColumnIdx++, forecastPrice , priceBoldStyle, Cell.CELL_TYPE_NUMERIC );
            setCellValue( row, nextColumnIdx++, backlogQty , productValueBoldStyle, Cell.CELL_TYPE_NUMERIC );
            setCellValue( row, nextColumnIdx++, backlogPrice , priceBoldStyle, Cell.CELL_TYPE_NUMERIC );
            setCellValue( row, nextColumnIdx++, shipQty , productValueBoldStyle, Cell.CELL_TYPE_NUMERIC );
            setCellValue( row, nextColumnIdx++, shipPrice , priceBoldStyle, Cell.CELL_TYPE_NUMERIC );
            setCellValue( row, nextColumnIdx++, backlogShippedQty , productValueBoldStyle, Cell.CELL_TYPE_NUMERIC );
            setCellValue( row, nextColumnIdx++, backlogShippedPrice , priceBoldStyle, Cell.CELL_TYPE_NUMERIC );
            nextColumnIdx = headers.length;
        }

        return workbook;
    }
     private Cell mergeCells ( Sheet sheet, int startRow, int startCell, int endRow, int endCell, CellStyle cellStyle )
    {
        //sheet.addMergedRegion( new Region( startRow, startCell, endRow, endCell ) );
        sheet.addMergedRegion( new CellRangeAddress( startRow, endRow, startCell,endCell )) ;
        Cell cell = null;
        for ( int rowIdx = startRow; rowIdx <= endRow; rowIdx++ )
        {
            for ( int cellIdx = startCell; cellIdx <= endCell; cellIdx++ )
            {
                cell = getCellAt ( sheet.getRow ( rowIdx ), cellIdx );
                cell.setCellStyle ( cellStyle );
            }
        }
        return getCellAt ( sheet.getRow( startRow), startCell );
    }
    private Cell getCellAt ( Row row, int cellNumber )
    {
        Cell cell = null;
        cell = row.getCell( cellNumber );
        if ( cell == null )
        {
            cell = row.createCell ( cellNumber );
        }
        return cell;
    }
     private void setCellValue ( Cell cell, RichTextString value, CellStyle cellStyle )
    {
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle );
        }
        cell.setCellValue( value );
    }
     
     
    private Map<String, String> getMonthsHeaderMap ( String period, int fromYear, int qtrNo, int fromMonth, int showNext )
    {
        LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
        int toMonth = 0;
        if ( "byQuarter".equalsIgnoreCase( period ) )
        {
            if ( qtrNo == 1 )
            {
                fromMonth = 0;
                toMonth = 2;
            }
            else if ( qtrNo == 2 )
            {
                fromMonth = 3;
                toMonth = 5;
            }
            else if ( qtrNo == 3 )
            {
                fromMonth = 6;
                toMonth = 8;
            }
            else
            {
                fromMonth = 9;
                toMonth = 11;
            }
        }
        map.put ( "Price;"+fromMonth+";"+fromYear, "Price" );
        if ( "byQuarter".equalsIgnoreCase( period ) )
        {
            for ( int i=fromMonth; i <= toMonth; i++ )
            {
                map.put ( "Qty;"+i+";"+fromYear, DateUtil.getShortMonthName(i)+"-" + fromYear );
            }
            map.put ( "Revenue;0;1;3",  "Q"+ qtrNo + "-" + fromYear + " Revenue" );
        }
        else //byMonth
        {
            Calendar fromDate = Calendar.getInstance();
            fromDate.clear();
            fromDate.set( fromYear, fromMonth, 1 );
            Calendar toDate = Calendar.getInstance();
            toDate.clear();
            toDate.setTime( fromDate.getTime() );

            if ( showNext > 0 )
            {
                toDate.add( Calendar.MONTH, (showNext-1) );//showNext includes the from month
            }
            Calendar tmpDate = (Calendar)fromDate.clone();

            while ( tmpDate.getTimeInMillis() <= toDate.getTimeInMillis() )
            {
                map.put ( "Qty;"+tmpDate.get(Calendar.MONTH)+";"+tmpDate.get(Calendar.YEAR), DateUtil.getShortMonthName(tmpDate.get(Calendar.MONTH))+"-" + tmpDate.get(Calendar.YEAR) );
                tmpDate.add ( Calendar.MONTH, 1 );
            }

            /*for ( int i=fromMonth; i <= (fromMonth>toMonth ? 11 : toMonth); i++ )
            {
                map.put ( "Qty;"+i+";"+fromYear, DateUtil.getShortMonthName(i)+"-" + fromYear );
            }
            if ( fromMonth > toMonth )
            {
                for ( int i=0; i <= toMonth; i++ )
                {
                    map.put ( "Qty;"+i+";"+(fromYear+1), DateUtil.getShortMonthName(i)+"-" + (fromYear+1) );
                }
            }*/
            int idx = 1;
            tmpDate = (Calendar)fromDate.clone();

            while ( tmpDate.getTimeInMillis() <= toDate.getTimeInMillis() )
            {
                //map.put ( "Qty;"+tmpDate.get(Calendar.MONTH)+";"+tmpDate.get(Calendar.YEAR), DateUtil.getShortMonthName(tmpDate.get(Calendar.MONTH))+"-" + tmpDate.get(Calendar.YEAR) );
                map.put ( "Revenue;0;"+idx+";"+idx, DateUtil.getShortMonthName(tmpDate.get(Calendar.MONTH))+"-" + tmpDate.get(Calendar.YEAR) + " Revenue" );
                idx++;
                tmpDate.add ( Calendar.MONTH, 1 );
            }
            /*for ( int i=fromMonth; i <= (fromMonth>toMonth ? 11 : toMonth); i++ )
            {
                map.put ( "Revenue;0;"+idx+";"+idx, DateUtil.getShortMonthName(i)+"-" + fromYear + " Revenue" );
                idx++;
            }
            if ( fromMonth > toMonth )
            {
                for ( int i=0; i <= toMonth; i++ )
                {
                    map.put ( "Revenue;0;"+idx+";"+idx, DateUtil.getShortMonthName(i)+"-" + (fromYear+1) + " Revenue" );
                    idx++;
                }
            }*/
        }
        return map;
    }

    private Map<String, String> getMonthsHeaderAnalysisFormat ( int fromYear, int fromMonth, int showNext )
    {
        LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
        Calendar fromDate = Calendar.getInstance();
        fromDate.clear();
        fromDate.set( fromYear, fromMonth, 1 );
        Calendar toDate = Calendar.getInstance();
        toDate.clear();
        toDate.setTime( fromDate.getTime() );

        if ( showNext > 0 )
        {
            toDate.add( Calendar.MONTH, (showNext-1) );//showNext includes the from month
        }
        int idx = 1;
        Calendar tmpDate = (Calendar)fromDate.clone();

        while ( tmpDate.getTimeInMillis() <= toDate.getTimeInMillis() )
        {
            map.put ( "Qty;"+tmpDate.get(Calendar.MONTH)+";"+tmpDate.get(Calendar.YEAR), DateUtil.getShortMonthName(tmpDate.get(Calendar.MONTH))+"-" + tmpDate.get(Calendar.YEAR) );
            map.put ( "Price;"+tmpDate.get(Calendar.MONTH)+";"+tmpDate.get(Calendar.YEAR), "Price" );
            map.put ( "Revenue;0;"+idx+";"+idx, DateUtil.getShortMonthName(tmpDate.get(Calendar.MONTH))+"-" + tmpDate.get(Calendar.YEAR) + " Revenue" );
            
            map.put ( "Orig Qty;"+tmpDate.get(Calendar.MONTH)+";"+tmpDate.get(Calendar.YEAR), DateUtil.getShortMonthName(tmpDate.get(Calendar.MONTH))+"-" + tmpDate.get(Calendar.YEAR) );
            map.put ( "Orig Price;"+tmpDate.get(Calendar.MONTH)+";"+tmpDate.get(Calendar.YEAR), "Price" );
            map.put ( "Orig Revenue;0;"+idx+";"+idx, DateUtil.getShortMonthName(tmpDate.get(Calendar.MONTH))+"-" + tmpDate.get(Calendar.YEAR) + " Revenue" );

            idx++;
            tmpDate.add ( Calendar.MONTH, 1 );
        }

        return map;
    }

    
    private Map<String, String> getMonthsHeaderMapNewFormat ( String period, int fromYear, int qtrNo, int fromMonth, int showNext )
    {
        LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
        int toMonth = 0;
        if ( "byQuarter".equalsIgnoreCase( period ) )
        {
            if ( qtrNo == 1 )
            {
                fromMonth = 0;
                toMonth = 2;
            }
            else if ( qtrNo == 2 )
            {
                fromMonth = 3;
                toMonth = 5;
            }
            else if ( qtrNo == 3 )
            {
                fromMonth = 6;
                toMonth = 8;
            }
            else
            {
                fromMonth = 9;
                toMonth = 11;
            }
        }

        if ( "byQuarter".equalsIgnoreCase( period ) )
        {
            for ( int i=fromMonth; i <= toMonth; i++ )
            {
                map.put ( "Qty;"+i+";"+fromYear, DateUtil.getShortMonthName(i)+"-" + fromYear );
                map.put ( "Price;"+i+";"+fromYear, "Price" );
                map.put ( "Revenue;0;"+i+";3",  "Q"+ qtrNo + "-" + fromYear + " Revenue" );
            }
        }
        else //byMonth
        {
            Calendar fromDate = Calendar.getInstance();
            fromDate.clear();
            fromDate.set( fromYear, fromMonth, 1 );
            Calendar toDate = Calendar.getInstance();
            toDate.clear();
            toDate.setTime( fromDate.getTime() );

            if ( showNext > 0 )
            {
                toDate.add( Calendar.MONTH, (showNext-1) );//showNext includes the from month
            }
            int idx = 1;
            Calendar tmpDate = (Calendar)fromDate.clone();

            while ( tmpDate.getTimeInMillis() <= toDate.getTimeInMillis() )
            {
                map.put ( "Qty;"+tmpDate.get(Calendar.MONTH)+";"+tmpDate.get(Calendar.YEAR), DateUtil.getShortMonthName(tmpDate.get(Calendar.MONTH))+"-" + tmpDate.get(Calendar.YEAR) );
                map.put ( "Price;"+tmpDate.get(Calendar.MONTH)+";"+tmpDate.get(Calendar.YEAR), "Price" );
                map.put ( "Revenue;0;"+idx+";"+idx, DateUtil.getShortMonthName(tmpDate.get(Calendar.MONTH))+"-" + tmpDate.get(Calendar.YEAR) + " Revenue" );
                idx++;
                tmpDate.add ( Calendar.MONTH, 1 );
            }
        }
        return map;
    }
    private void setCellValue ( Row row, int cellNo, RichTextString value, CellStyle cellStyle )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
    }
    private void setCellValue ( Row row, int cellNo, double value, CellStyle cellStyle, int cellType )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
        cell.setCellType( cellType );
    }
    /**
     *
     * @param idx - zero based index
     *
     * @return
     */
    private String getAlphaforIdx ( int idx )
    {
        String[] alpha = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"};
        String formatted = "";
        if ( idx < alpha.length )
        {
            formatted = alpha[idx];
        }
        else
        {
            //idx=idx+1;
            int idx1 = (idx/26)-1;
            int idx2 = (idx%26);
            if ( idx1 >= 0 )
            {
                formatted = alpha[idx1];
            }
            if ( idx2 >= 0 )
            {
                formatted = formatted+alpha[idx2];
            }
        }
        return formatted;
    }
    private String getCellReference ( int columnIdx, int rowNumber )
    {
        return getAlphaforIdx( columnIdx ) + rowNumber;
    }
    private void setCellFormula ( Row row, int cellNo, String formula, CellStyle cellStyle )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellFormula( formula );
    }
    private boolean isRowDataInSearchCriteria( RowData rowData, SalesForecastReportForm salesForecastReportForm ) {
        if( !isIdExist(rowData.getBaseProductId(), salesForecastReportForm.getProductIds()) ){
            return false;
        }
        if( !isIdExist(rowData.getProductId(), salesForecastReportForm.getProcessStepIds()) ){
            return false;
        }
        if( !isIdExist(rowData.getSalesPersonId(), salesForecastReportForm.getSalesPersonIds()) ){
            return false;
        }
        if( !isIdExist(rowData.getCustId(), salesForecastReportForm.getCustomerIds()) ){
            return false;
        }
        return true;
    }
    private boolean isIdExist(int id, int[] ids) {
        boolean exist = false;
        if(ids == null){
            exist = true;
        } else {        
            for(int i=0; i<ids.length; i++){
                if ( ids[i] <= 0 || ids[i] == id){
                    exist = true;
                    break;
                }
            }
        }
        return exist;
    }
    private boolean isSlotDataInSearchCriteria(SlotData slotData, SalesForecastReportForm salesForecastReportForm) {
        long from = Long.parseLong(salesForecastReportForm.getYear()+""+salesForecastReportForm.getFromMonth());
        long to = Long.parseLong(salesForecastReportForm.getToYear()+""+salesForecastReportForm.getToMonth());
        long val = Long.parseLong(slotData.getYear()+""+slotData.getMonth());
        return val>=from && val<=to;
    }
    private LinkedList<RowData> getSelectedList(LinkedList<RowData> forecastDataList){
        LinkedList<RowData> selectedList = new LinkedList<RowData>();
        for(RowData rowData:forecastDataList){
            if(rowData.isSelected()){
                selectedList.add(rowData);
            }
        }
        return selectedList;
    }
    private String prepareSearchParamsStr(SalesForecastReportForm salesForecastReportForm) {
        StringBuffer sb = new StringBuffer();
        final String delim = ";";
        sb.append("qty").append("=").append(salesForecastReportForm.getQty()).append(delim);
        sb.append("qtyType").append("=").append(salesForecastReportForm.getQtyType()).append(delim);
        sb.append("price").append("=").append(salesForecastReportForm.getPrice()).append(delim);
        sb.append("priceType").append("=").append(salesForecastReportForm.getPriceType()).append(delim);
        sb.append("fromMonth").append("=").append(salesForecastReportForm.getFromMonth()).append(delim);
        sb.append("fromYear").append("=").append(salesForecastReportForm.getYear()).append(delim);
        sb.append("toMonth").append("=").append(salesForecastReportForm.getToMonth()).append(delim);
        sb.append("toYear").append("=").append(salesForecastReportForm.getToYear()).append(delim);
        sb.append("productIds").append("=").append(concatIds(salesForecastReportForm.getProductIds())).append(delim);
        sb.append("processStepIds").append("=").append(concatIds(salesForecastReportForm.getProcessStepIds())).append(delim);
        sb.append("salesPersonIds").append("=").append(concatIds(salesForecastReportForm.getSalesPersonIds())).append(delim);
        sb.append("customerIds").append("=").append(concatIds(salesForecastReportForm.getCustomerIds())).append(delim);

        return sb.toString();
    }
    private String concatIds(int[] ids){
        StringBuffer sb = new StringBuffer();
        if(ids != null) {
            for ( int i=0; i < ids.length; i++) {
                sb.append(ids[i]).append(",");
            }
        }
        return sb.toString();
    }
    private SalesForecastReportForm getFilledForecastReportForm(String paramsStr){
        SalesForecastReportForm salesForecastReportForm = new SalesForecastReportForm();
        String[] nameValuePairs = paramsStr.split(";");
        if( nameValuePairs != null ) {
            for ( int i=0; i<nameValuePairs.length; i++) {
                String[] nameValue = nameValuePairs[i].split("=");
                if("qty".equalsIgnoreCase(nameValue[0])){
                    if( nameValue.length>1 ) {
                        salesForecastReportForm.setQty(Integer.parseInt(nameValue[1]));
                    }
                } else if("qtyType".equalsIgnoreCase(nameValue[0])){
                    if( nameValue.length>1 ) {
                        salesForecastReportForm.setQtyType(nameValue[1]);
                    }
                } else if("qtyType".equalsIgnoreCase(nameValue[0])){
                    if( nameValue.length>1 ) {
                        salesForecastReportForm.setQtyType(nameValue[1]);
                    }
                } else if("price".equalsIgnoreCase(nameValue[0])){
                    if( nameValue.length>1 ) {
                        salesForecastReportForm.setPrice(Integer.parseInt(nameValue[1]));
                    }
                } else if("priceType".equalsIgnoreCase(nameValue[0])){
                    if( nameValue.length>1 ) {
                        salesForecastReportForm.setPriceType(nameValue[1]);
                    }
                } else if("fromMonth".equalsIgnoreCase(nameValue[0])){
                    if( nameValue.length>1 ) {
                        salesForecastReportForm.setFromMonth(Integer.parseInt(nameValue[1]));
                    }
                } else if("fromYear".equalsIgnoreCase(nameValue[0])){
                    if( nameValue.length>1 ) {
                        salesForecastReportForm.setYear(Integer.parseInt(nameValue[1]));
                    }
                } else if("toMonth".equalsIgnoreCase(nameValue[0])){
                    if( nameValue.length>1 ) {
                        salesForecastReportForm.setToMonth(Integer.parseInt(nameValue[1]));
                    }
                } else if("toYear".equalsIgnoreCase(nameValue[0])){
                    if( nameValue.length>1 ) {
                        salesForecastReportForm.setToYear(Integer.parseInt(nameValue[1]));
                    }
                } else if("productIds".equalsIgnoreCase(nameValue[0])){
                    if( nameValue.length>1 ) {
                        salesForecastReportForm.setProductIds(toIntArray(nameValue[1]));
                    }
                } else if("processStepIds".equalsIgnoreCase(nameValue[0])){
                    if( nameValue.length>1 ) {
                        salesForecastReportForm.setProcessStepIds(toIntArray(nameValue[1]));
                    }
                } else if("salesPersonIds".equalsIgnoreCase(nameValue[0])){
                    if( nameValue.length>1 ) {
                        salesForecastReportForm.setSalesPersonIds(toIntArray(nameValue[1]));
                    }
                } else if("customerIds".equalsIgnoreCase(nameValue[0])){
                    if( nameValue.length>1 ) {
                        salesForecastReportForm.setCustomerIds(toIntArray(nameValue[1]));
                    }
                }
            }
        }
        return salesForecastReportForm;
    }
    private int[] toIntArray(String idsStr){
        int[] ids = null;
        if(idsStr != null && idsStr.trim().length() > 0){
            String[] idsStrArr = idsStr.split(",");
            ids = new int[idsStrArr.length];
            for(int i=0; i < idsStrArr.length; i++){
                ids[i] = Integer.parseInt(idsStrArr[i]);
            }
        }
        return ids;
    }

    private Workbook generateSnapshotWorkBook(LinkedList forecastDataList, Map monthsHeaderMap, String snapshotname) {
        Workbook workbook = null;
        Sheet sheet = null;
        if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
        {
            workbook    = new XSSFWorkbook();
        }
        else
        {
            workbook    = new HSSFWorkbook();
        }
        CreationHelper ch = workbook.getCreationHelper();
        DataFormat df = workbook.createDataFormat();
        sheet = workbook.createSheet("Forecast Snapshot Report");
        sheet.setSelected(true);

        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 10);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titleFont.setFontName("verdana");
        titleFont.setColor(IndexedColors.BLACK.getIndex());

        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 8);
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontName("verdana");
        headerFont.setColor(IndexedColors.BLACK.getIndex());

        Font textFont = workbook.createFont();
        textFont.setFontHeightInPoints((short) 8);
        textFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        textFont.setFontName("verdana");
        textFont.setColor(IndexedColors.BLACK.getIndex());

        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setBorderBottom(CellStyle.BORDER_THIN);
        titleStyle.setBorderTop(CellStyle.BORDER_THIN);
        titleStyle.setBorderLeft(CellStyle.BORDER_THIN);
        titleStyle.setBorderRight(CellStyle.BORDER_THIN);
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setWrapText(true);
        titleStyle.setFont(titleFont);

        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
        headerStyle.setBorderTop(CellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
        headerStyle.setBorderRight(CellStyle.BORDER_THIN);
        headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        headerStyle.setWrapText(true);
        headerStyle.setFont(headerFont);

        CellStyle productValueStyle = workbook.createCellStyle();
        productValueStyle.setBorderBottom(CellStyle.BORDER_THIN);
        productValueStyle.setBorderTop(CellStyle.BORDER_THIN);
        productValueStyle.setBorderLeft(CellStyle.BORDER_THIN);
        productValueStyle.setBorderRight(CellStyle.BORDER_THIN);
        productValueStyle.setAlignment(CellStyle.ALIGN_RIGHT);
        productValueStyle.setWrapText(true);
        productValueStyle.setFont(textFont);
        productValueStyle.setDataFormat(df.getFormat("#,##0"));

        CellStyle productValueBoldStyle = workbook.createCellStyle();
        productValueBoldStyle.setBorderBottom(CellStyle.BORDER_THIN);
        productValueBoldStyle.setBorderTop(CellStyle.BORDER_THIN);
        productValueBoldStyle.setBorderLeft(CellStyle.BORDER_THIN);
        productValueBoldStyle.setBorderRight(CellStyle.BORDER_THIN);
        productValueBoldStyle.setAlignment(CellStyle.ALIGN_RIGHT);
        productValueBoldStyle.setWrapText(true);
        productValueBoldStyle.setFont(headerFont);
        productValueBoldStyle.setDataFormat(df.getFormat("#,##0"));

        CellStyle priceStyle = workbook.createCellStyle();
        priceStyle.setBorderBottom(CellStyle.BORDER_THIN);
        priceStyle.setBorderTop(CellStyle.BORDER_THIN);
        priceStyle.setBorderLeft(CellStyle.BORDER_THIN);
        priceStyle.setBorderRight(CellStyle.BORDER_THIN);
        priceStyle.setAlignment(CellStyle.ALIGN_RIGHT);
        priceStyle.setWrapText(true);
        priceStyle.setFont(textFont);
        priceStyle.setDataFormat(df.getFormat("($#,##0.00_);[Red]($#,##0.00)"));

        CellStyle priceBoldStyle = workbook.createCellStyle();
        priceBoldStyle.setBorderBottom(CellStyle.BORDER_THIN);
        priceBoldStyle.setBorderTop(CellStyle.BORDER_THIN);
        priceBoldStyle.setBorderLeft(CellStyle.BORDER_THIN);
        priceBoldStyle.setBorderRight(CellStyle.BORDER_THIN);
        priceBoldStyle.setAlignment(CellStyle.ALIGN_RIGHT);
        priceBoldStyle.setWrapText(true);
        priceBoldStyle.setFont(headerFont);
        priceBoldStyle.setDataFormat(df.getFormat("($#,##0.00_);[Red]($#,##0.00)"));

        CellStyle productStyle = workbook.createCellStyle();
        productStyle.setBorderBottom(CellStyle.BORDER_THIN);
        productStyle.setBorderTop(CellStyle.BORDER_THIN);
        productStyle.setBorderLeft(CellStyle.BORDER_THIN);
        productStyle.setBorderRight(CellStyle.BORDER_THIN);
        productStyle.setAlignment(CellStyle.ALIGN_LEFT);
        productStyle.setWrapText(true);
        productStyle.setFont(textFont);

        //SETTING HEADINGS
        String headers[] = null;
        String subheaders[] = null;
        headers = new String[]{"Customer", "Sales Person", "Project", "Part Number"};
        subheaders = new String[]{"Qty", "ASP", "Rev"};
        int showNext = monthsHeaderMap.size()/3;

        // First Row
        Row row = sheet.createRow(0);
        Cell cell = mergeCells(sheet, 0, 0, 0, ((headers.length - 1) + (2 * showNext) + 1), titleStyle);
        setCellValue(cell, ch.createRichTextString("Forecast Snapshot Report-"+snapshotname), titleStyle);

        // Header Row
        row = sheet.createRow(1);
        cell = mergeCells(sheet, 1, 0, 1, (headers.length - 1), titleStyle);
        setCellValue(cell, ch.createRichTextString(""), headerStyle);
        int colNum = headers.length;
        Collection<String> mthHeaders = monthsHeaderMap.values();
        Iterator<String> monthIterator = mthHeaders.iterator();

        while (monthIterator.hasNext()) {
            String monthItrValue = (String) monthIterator.next();
            if (monthItrValue != null && monthItrValue.indexOf("Price") < 0 && monthItrValue.indexOf("Revenue") < 0) {
                int endCell = colNum + 2;
                cell = mergeCells(sheet, 1, colNum, 1, endCell, titleStyle);
                setCellValue(cell, ch.createRichTextString(monthItrValue), headerStyle);
                int width = (monthItrValue.length() > 12) ? monthItrValue.length() : 12;
                sheet.setColumnWidth(colNum, (((width + 2) * 256)));
                colNum = colNum + 3;
            }
        }
        row = sheet.createRow(2);

        for (int i = 0; i < headers.length; i++) {
            cell = row.createCell(i);
            setCellValue(cell, ch.createRichTextString(headers[i]), headerStyle);
            int width = (headers[i].length() > 12) ? headers[i].length() : 12;
            sheet.setColumnWidth(i, (((width + 2) * 256)));
        }
        int row2colNum = headers.length;
        for (int i = 0; i < showNext; i++) {
            for (int j = 0; j < subheaders.length; j++) {
                cell = row.createCell(row2colNum);
                setCellValue(cell, ch.createRichTextString(subheaders[j]), headerStyle);
                int width = (subheaders[j].length() > 12) ? subheaders[j].length() : 12;
                sheet.setColumnWidth(row2colNum, (((width + 2) * 256)));
                row2colNum++;
            }
        }

        int nextRowNumber = 3;
        int nextColumnIdx = 0;
        for (int i = 0; i < forecastDataList.size(); i++) {
            RowData rowData = (RowData) forecastDataList.get(i);
            row = sheet.createRow(nextRowNumber++);
            nextColumnIdx = 0;
            setCellValue(row, nextColumnIdx++, ch.createRichTextString(rowData.getCustomerName()), productStyle);
            setCellValue(row, nextColumnIdx++, ch.createRichTextString(rowData.getSalesPersonName()), productStyle);
            setCellValue(row, nextColumnIdx++, ch.createRichTextString(rowData.getProjName()), productStyle);
            setCellValue(row, nextColumnIdx++, ch.createRichTextString(rowData.getProductName()), productStyle);

            Iterator<String> monthKeyItr = monthsHeaderMap.keySet().iterator();
            while (monthKeyItr.hasNext()) {
                String itrValue = (String) monthKeyItr.next();
                //	System.out.println("itrValue = " + itrValue);
                if (itrValue.startsWith("Qty")) {
                    String[] split = itrValue.split("\\;");
                    int month = Integer.parseInt(split[1]);
                    int year = Integer.parseInt(split[2]);
                    SlotData slotData = rowData.getSlotData(month, year);
                    if (slotData != null)
                        setCellValue(row, nextColumnIdx++, slotData.getAcQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC);
                    else
                        setCellValue(row, nextColumnIdx++, ch.createRichTextString("0"), productValueStyle);
                } else if (itrValue.startsWith("Price")) {
                    String[] split = itrValue.split("\\;");
                    int month = Integer.parseInt(split[1]);
                    int year = Integer.parseInt(split[2]);
                    SlotData slotData = rowData.getSlotData(month, year);
                    if (slotData != null)
                        setCellValue(row, nextColumnIdx++, slotData.getAcPrice(), priceStyle, Cell.CELL_TYPE_NUMERIC);
                    else
                        setCellValue(row, nextColumnIdx++, ch.createRichTextString("0.0"), priceStyle);
                } else if (itrValue.startsWith("Revenue")) {
                    int priceIdx = (nextColumnIdx - 1);
                    int fromMthIdx = (nextColumnIdx - 2);

                    String formula = getCellReference(fromMthIdx, nextRowNumber) + "*" + getCellReference(priceIdx, nextRowNumber);
                    setCellFormula(row, nextColumnIdx++, formula, priceStyle);
                }
            }
        }
        //--TOTAL ROW
        if (nextRowNumber != 2) //data available
        {
            row = sheet.createRow(nextRowNumber);
            cell = mergeCells(sheet, nextRowNumber, 0, nextRowNumber, (headers.length - 1), titleStyle);
            setCellValue(cell, ch.createRichTextString("Total"), titleStyle);
            Iterator<String> monthKeyItr = monthsHeaderMap.keySet().iterator();
            nextColumnIdx = headers.length;
            while (monthKeyItr.hasNext()) {
                String itrValue = (String) monthKeyItr.next();
                if (itrValue.startsWith("Qty")) {
                    String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
                    setCellFormula(row, nextColumnIdx, totalFormula, productValueBoldStyle);
                } else if (itrValue.startsWith("Revenue")) {
                    String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
                    setCellFormula(row, nextColumnIdx, totalFormula, priceBoldStyle);
                }
                if (itrValue.startsWith("Orig Qty")) {
                    String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
                    setCellFormula(row, nextColumnIdx, totalFormula, productValueBoldStyle);
                } else if (itrValue.startsWith("Orig Revenue")) {
                    String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
                    setCellFormula(row, nextColumnIdx, totalFormula, priceBoldStyle);
                }

                nextColumnIdx++;
            }
        }

        return workbook;
    }

    private void mergeReportsToCompare(LinkedList report1, LinkedList report2) {
        int size = report2.size();
        for(int i=size-1; i>=0; i--) {
            RowData rowData2 = (RowData)report2.get(i);
            RowData rowData1 = getRowDataFromList(report1, rowData2.getProjId(), rowData2.getProductId());
            if(rowData1 != null) { //rowdata2 exists in report1, need to merge slot data.
                mergeSlotDataToCompare(rowData1.getSlotDataList(), rowData2.getSlotDataList());
                //todo need to sort rowData1.getSlotDataList()
            } else { //rowdata2 does not exists, need to merge to the new row data.
                List slotDataList2 = rowData2.getSlotDataList();
                AutoGrowingLinkedList slotDataList1 = new AutoGrowingLinkedList(SlotData.class);
                mergeSlotDataToCompare(slotDataList1, slotDataList2);
                //todo need to sort slotDataList1
                rowData2.setSlotDataList(slotDataList1);

                report1.add(rowData2);
            }
        }
    }

    /**
     * merges to the list1
     * @param slotDataList1
     * @param slotDataList2
     */
    private void mergeSlotDataToCompare(List slotDataList1, List slotDataList2) {
        int size = slotDataList2.size();
        for(int i=size-1; i>=0; i--) {
            SlotData slotData2 = (SlotData)slotDataList2.get(i);
            SlotData slotData1 = getSlotDataFromList(slotDataList1, slotData2.getMonth(), slotData2.getYear());
            slotData1.setQty(slotData2.getAcQty());
            slotData1.setPrice(slotData2.getAcPrice());
        }

    }

    private RowData getRowDataFromList(List rowDataList, int projId, int prodId) {
        RowData rowData = null;
        for(int i = 0; i < rowDataList.size(); i++) {
            RowData tmp = (RowData)rowDataList.get(i);
            if(tmp.getProjId() == projId && tmp.getProductId() == prodId) {
                rowData = tmp;
                break;
            }
        }

        return rowData;
    }

    private SlotData getSlotDataFromList(List slotDataList, int month, int year) {
        SlotData slotData = null;
        for(int i = 0; i < slotDataList.size(); i++) {
            SlotData tmp = (SlotData)slotDataList.get(i);
            if(tmp.getMonth() == month && tmp.getYear() == year) {
                slotData = tmp;
                break;
            }
        }
        if(slotData == null) { //notfound, need to add..
            slotData = new SlotData();
            slotData.setMonth(month);
            slotData.setYear(year);
            slotDataList.add(slotData);
        }
        return slotData;
    }
    private LinkedHashMap<String, String> getMonthsHeaderMap(LinkedList rowDataList) {
        LinkedHashMap<String, String> monthsHeaderMap = new LinkedHashMap<String, String>();
        int idx = 0;
        for(int i=0; i<rowDataList.size(); i++) {
            RowData rowData = (RowData)rowDataList.get(i);
            List slotDataList = rowData.getSlotDataList();
            for(int j=0; j<slotDataList.size(); j++) {
                SlotData slotData = (SlotData)slotDataList.get(j);
                int month = slotData.getMonth();
                int year = slotData.getYear();
               if(!monthsHeaderMap.containsKey("Qty;"+month+";"+year)) {
                    monthsHeaderMap.put ( "Qty;"+month+";"+year, DateUtil.getShortMonthName(month)+"-" + year );
                    monthsHeaderMap.put ( "Price;"+month+";"+year, "Price" );
                    monthsHeaderMap.put ( "Revenue;0;"+idx+";"+idx, DateUtil.getShortMonthName(month)+"-" + year + " Revenue" );

                    monthsHeaderMap.put ( "Rpt2 Qty;"+month+";"+year, DateUtil.getShortMonthName(month)+"-" + year );
                    monthsHeaderMap.put ( "Rpt2 Price;"+month+";"+year, "Price" );
                    monthsHeaderMap.put ( "Rpt2 Revenue;0;"+idx+";"+idx, DateUtil.getShortMonthName(month)+"-" + year + " Revenue" );
                    idx++;
                }
            }
        }
        return monthsHeaderMap;
    }

    private Workbook generateCompareWorkBook( LinkedList forecastDataList, String reportname1, String reportname2) {
		Workbook workbook   = null;
		Sheet sheet       = null;
		if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
        {
            workbook    = new XSSFWorkbook();
        }
        else
        {
            workbook    = new HSSFWorkbook();
        }
        CreationHelper ch = workbook.getCreationHelper();
        DataFormat df = workbook.createDataFormat();
		sheet       = workbook.createSheet( "Forecast Compare Report" );
		sheet.setSelected( true );

		Font titleFont = workbook.createFont();
		titleFont.setFontHeightInPoints ( ( short ) 10 );
		titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
		titleFont.setFontName( "verdana" );
		titleFont.setColor ( IndexedColors.BLACK.getIndex());

		Font headerFont = workbook.createFont();
		headerFont.setFontHeightInPoints ( ( short ) 8 );
		headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
		headerFont.setFontName( "verdana" );
		headerFont.setColor ( IndexedColors.BLACK.getIndex());

		Font textFont = workbook.createFont();
		textFont.setFontHeightInPoints ( ( short ) 8 );
		textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
		textFont.setFontName( "verdana" );
		textFont.setColor ( IndexedColors.BLACK.getIndex());

		CellStyle titleStyle  = workbook.createCellStyle ( );
		titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
		titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
		titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
		titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
		titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
		titleStyle.setWrapText ( true );
		titleStyle.setFont ( titleFont );

		CellStyle headerStyle  = workbook.createCellStyle ( );
		headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
		headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
		headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
		headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
		headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
		headerStyle.setWrapText ( true );
		headerStyle.setFont ( headerFont );

		CellStyle productValueStyle  = workbook.createCellStyle ( );
		productValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
		productValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
		productValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
		productValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
		productValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
		productValueStyle.setWrapText ( true );
		productValueStyle.setFont ( textFont );
		productValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

		CellStyle productValueBoldStyle  = workbook.createCellStyle ( );
		productValueBoldStyle.setBorderBottom ( CellStyle.BORDER_THIN );
		productValueBoldStyle.setBorderTop ( CellStyle.BORDER_THIN );
		productValueBoldStyle.setBorderLeft ( CellStyle.BORDER_THIN );
		productValueBoldStyle.setBorderRight ( CellStyle.BORDER_THIN );
		productValueBoldStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
		productValueBoldStyle.setWrapText ( true );
		productValueBoldStyle.setFont ( headerFont );
		productValueBoldStyle.setDataFormat( df.getFormat( "#,##0" ) );

		CellStyle priceStyle  = workbook.createCellStyle ( );
		priceStyle.setBorderBottom ( CellStyle.BORDER_THIN );
		priceStyle.setBorderTop ( CellStyle.BORDER_THIN );
		priceStyle.setBorderLeft ( CellStyle.BORDER_THIN );
		priceStyle.setBorderRight ( CellStyle.BORDER_THIN );
		priceStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
		priceStyle.setWrapText ( true );
		priceStyle.setFont ( textFont );
		priceStyle.setDataFormat( df.getFormat( "($#,##0.00_);[Red]($#,##0.00)" ) );

		CellStyle priceBoldStyle  = workbook.createCellStyle ( );
		priceBoldStyle.setBorderBottom ( CellStyle.BORDER_THIN );
		priceBoldStyle.setBorderTop ( CellStyle.BORDER_THIN );
		priceBoldStyle.setBorderLeft ( CellStyle.BORDER_THIN );
		priceBoldStyle.setBorderRight ( CellStyle.BORDER_THIN );
		priceBoldStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
		priceBoldStyle.setWrapText ( true );
		priceBoldStyle.setFont ( headerFont );
		priceBoldStyle.setDataFormat( df.getFormat( "($#,##0.00_);[Red]($#,##0.00)" ) );

		CellStyle productStyle  = workbook.createCellStyle ( );
		productStyle.setBorderBottom ( CellStyle.BORDER_THIN );
		productStyle.setBorderTop ( CellStyle.BORDER_THIN );
		productStyle.setBorderLeft ( CellStyle.BORDER_THIN );
		productStyle.setBorderRight ( CellStyle.BORDER_THIN );
		productStyle.setAlignment ( CellStyle.ALIGN_LEFT );
		productStyle.setWrapText ( true );
		productStyle.setFont ( textFont );

		//SETTING HEADINGS
		String headers[] = null;
		String subheaders[] = null;
		headers = new String[]{ "Customer",  "Sales Person", "Project", "Part Number" };
		subheaders = new String[]{ "Rpt1 Qty",  "Rpt1 ASP", "Rpt1 Rev", "Rpt2 Qty",  "Rpt2 ASP", "Rpt2 Rev"  };
        Map<String, String> monthsHeaderMap = getMonthsHeaderMap( forecastDataList );

		int showNext = monthsHeaderMap.size()/6;

		// First Row
        int rowIdx = 0;
		Row row = sheet.createRow( rowIdx );
		Cell cell = mergeCells ( sheet, rowIdx, 0, rowIdx, ( (headers.length-1) + (2*showNext ) + 1 ), titleStyle );
		setCellValue ( cell, ch.createRichTextString("Forecast Compare Report"), titleStyle );

        rowIdx = rowIdx + 1;
        row = sheet.createRow ( rowIdx );
        setCellValue(row, 0, ch.createRichTextString("Report1:"), headerStyle);
        setCellValue(row, 1, ch.createRichTextString(reportname1), headerStyle);
        setCellValue(row, 2, ch.createRichTextString("Report2:"), headerStyle);
        setCellValue(row, 3, ch.createRichTextString(reportname2), headerStyle);



		// Header Row
        rowIdx = rowIdx + 1;
		row = sheet.createRow ( rowIdx );
		cell    = mergeCells ( sheet, rowIdx, 0, rowIdx, (headers.length-1), titleStyle );
		setCellValue ( cell, ch.createRichTextString(""), headerStyle );
		int colNum = headers.length;
		Collection<String> mthHeaders = monthsHeaderMap.values();
		Iterator<String> monthIterator = mthHeaders.iterator();

		while ( monthIterator.hasNext() )
		{
			String monthItrValue = (String)monthIterator.next();
			if ( monthItrValue != null && monthItrValue.indexOf("Price") < 0 &&  monthItrValue.indexOf("Revenue") < 0 )
			{
				int endCell = colNum + 2;
				cell    = mergeCells ( sheet, rowIdx, colNum, rowIdx, endCell, titleStyle );
				setCellValue ( cell, ch.createRichTextString(monthItrValue), headerStyle );
				int width = ( monthItrValue.length() > 12 ) ? monthItrValue.length() : 12;
				sheet.setColumnWidth( colNum, ( ( ( width + 2 ) * 256 ) ) );
				colNum= colNum + 3;
			}
		}

        rowIdx = rowIdx + 1;
		row = sheet.createRow ( rowIdx );

		for ( int i = 0; i < headers.length; i++ )
		{
			cell    = row.createCell( i );
			setCellValue ( cell, ch.createRichTextString(headers[i]), headerStyle );
			int width = ( headers[i].length() > 12 ) ? headers[i].length() : 12;
			sheet.setColumnWidth( i, ( ( ( width + 2 ) * 256 ) ) );
		}
		int row2colNum =  headers.length;
		for ( int i= 0; i < showNext; i++ )
		{
			for ( int j=0; j < subheaders.length; j++ )
			{
				cell    = row.createCell( row2colNum );
				setCellValue ( cell, ch.createRichTextString(subheaders[j]), headerStyle );
				int width = ( subheaders[j].length() > 12 ) ? subheaders[j].length() : 12;
				sheet.setColumnWidth( row2colNum, ( ( ( width + 2 ) * 256 ) ) );
				row2colNum ++;
			}
		}

		int nextRowNumber = rowIdx+1;
		int nextColumnIdx = 0;
		for ( int i=0; i < forecastDataList.size(); i++ )
		{
			RowData rowData = (RowData) forecastDataList.get(i);
			row = sheet.createRow( nextRowNumber++ );
			nextColumnIdx = 0;
			setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getCustomerName()), productStyle  );
			setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getSalesPersonName()), productStyle  );
			setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getProjName()), productStyle  );
			setCellValue( row, nextColumnIdx++, ch.createRichTextString(rowData.getProductName()), productStyle  );

			Iterator<String> monthKeyItr = monthsHeaderMap.keySet().iterator();
			while ( monthKeyItr.hasNext() )
			{
				String itrValue = (String)monthKeyItr.next();
				//	System.out.println("itrValue = " + itrValue);
				if ( itrValue.startsWith( "Qty" ) )
				{
					String[] split = itrValue.split("\\;");
					int month = Integer.parseInt( split[1] );
					int year = Integer.parseInt( split[2] );
					SlotData slotData = rowData.getSlotData( month, year );
					if ( slotData != null )
						setCellValue( row, nextColumnIdx++, slotData.getAcQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
					else
						setCellValue( row, nextColumnIdx++, ch.createRichTextString("0"), productValueStyle  );
				}
				else if ( itrValue.startsWith( "Price" ) )
				{
					String[] split = itrValue.split("\\;");
					int month = Integer.parseInt( split[1] );
					int year = Integer.parseInt( split[2] );
					SlotData slotData = rowData.getSlotData( month, year );
					if ( slotData != null  )
						setCellValue( row, nextColumnIdx++, slotData.getAcPrice(), priceStyle, Cell.CELL_TYPE_NUMERIC  );
					else
						setCellValue( row, nextColumnIdx++, ch.createRichTextString("0.0"), priceStyle  );
				}
				else if ( itrValue.startsWith( "Revenue" ) )
				{
					int priceIdx = (nextColumnIdx-1);
					int fromMthIdx = (nextColumnIdx-2);

					String formula =  getCellReference(fromMthIdx, nextRowNumber) + "*" + getCellReference(priceIdx, nextRowNumber);
					setCellFormula( row, nextColumnIdx++, formula, priceStyle  );
				}
				else if ( itrValue.startsWith( "Rpt2 Qty" ) )
				{
					String[] split = itrValue.split("\\;");
					int month = Integer.parseInt( split[1] );
					int year = Integer.parseInt( split[2] );
					SlotData slotData = rowData.getSlotData( month, year );
					if ( slotData != null )
						setCellValue( row, nextColumnIdx++, slotData.getQty(), productValueStyle, Cell.CELL_TYPE_NUMERIC  );
					else
						setCellValue( row, nextColumnIdx++, ch.createRichTextString("0"), productValueStyle  );
				}
				else if ( itrValue.startsWith( "Rpt2 Price" ) )
				{
					String[] split = itrValue.split("\\;");
					int month = Integer.parseInt( split[1] );
					int year = Integer.parseInt( split[2] );
					SlotData slotData = rowData.getSlotData( month, year );
					if ( slotData != null  )
						setCellValue( row, nextColumnIdx++, slotData.getPrice(), priceStyle, Cell.CELL_TYPE_NUMERIC  );
					else
						setCellValue( row, nextColumnIdx++, ch.createRichTextString("0.0"), priceStyle  );
				}
				else if ( itrValue.startsWith( "Rpt2 Revenue" ) )
				{
					int priceIdx = (nextColumnIdx-1);
					int fromMthIdx = (nextColumnIdx-2);

					String formula =  getCellReference(fromMthIdx, nextRowNumber) + "*" + getCellReference(priceIdx, nextRowNumber);
					setCellFormula( row, nextColumnIdx++, formula, priceStyle  );
				}
			}
		}
		//--TOTAL ROW
		if ( nextRowNumber != 2 ) //data available
		{
			row = sheet.createRow( nextRowNumber );
			cell = mergeCells ( sheet, nextRowNumber, 0, nextRowNumber, (headers.length-1), titleStyle );
			setCellValue ( cell, ch.createRichTextString("Total"), titleStyle );
			Iterator<String> monthKeyItr = monthsHeaderMap.keySet().iterator();
			nextColumnIdx = headers.length;
			while ( monthKeyItr.hasNext() )
			{
				String itrValue = (String)monthKeyItr.next();
				if ( itrValue.startsWith( "Qty" ) )
				{
					String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
					setCellFormula( row, nextColumnIdx, totalFormula, productValueBoldStyle );
				}
				else if ( itrValue.startsWith( "Revenue" ) )
				{
					String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
					setCellFormula( row, nextColumnIdx, totalFormula, priceBoldStyle );
				}
				if ( itrValue.startsWith( "Rpt2 Qty" ) )
				{
					String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
					setCellFormula( row, nextColumnIdx, totalFormula, productValueBoldStyle );
				}
				else if ( itrValue.startsWith( "Rpt2 Revenue" ) )
				{
					String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 3) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
					setCellFormula( row, nextColumnIdx, totalFormula, priceBoldStyle );
				}

				nextColumnIdx++;
			}
		}

		return workbook;
	}

    private Workbook generateWaterFallChart(LinkedList forecastDataList, SalesForecastReportForm salesForecastReportForm, String reportname, int startMonth, int startYear, int showNextFCDetails) throws ScalarException {
        Workbook workbook = null;
        Sheet sheet = null;
        if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
        {
            workbook    = new XSSFWorkbook();
        }
        else
        {
            workbook    = new HSSFWorkbook();
        }
        CreationHelper ch = workbook.getCreationHelper();
        DataFormat df = workbook.createDataFormat();
        sheet = workbook.createSheet(reportname);
        sheet.setSelected(true);

        ExcelUtils excelUtils = new ExcelUtils(workbook);

        LinkedHashMap<String, String> monthsHeadingsMap = new LinkedHashMap<String, String>();
        Calendar cal = Calendar.getInstance();
        cal.set(startYear, startMonth, 1);
        int count = 0;
        while (count < showNextFCDetails) {
            String heading = DateUtil.getMonthsArray()[cal.
                    get(Calendar.MONTH)] + "-" + cal.get(Calendar.YEAR);
            if (REPORT_TYPE_QUANTITY.equalsIgnoreCase(salesForecastReportForm.getReportType())) {
                monthsHeadingsMap.put("Qty;" + cal.get(Calendar.MONTH) + ";" + cal.get(Calendar.YEAR), heading);
            } else if (REPORT_TYPE_REVENUE.equalsIgnoreCase(salesForecastReportForm.getReportType())) {
                monthsHeadingsMap.put("Revenue;" + cal.get(Calendar.MONTH) + ";" + cal.get(Calendar.YEAR), heading);
            }
            cal.add(Calendar.MONTH, 1);
            count++;
        }


        // First Row
        int rowIdx = 0;
        Row row = sheet.createRow(rowIdx);
        Cell cell = excelUtils.mergeCells(sheet, rowIdx, 0, rowIdx, monthsHeadingsMap.size(), excelUtils.CELL_STYLE_TITLE);
        excelUtils.setCellValue(cell, ch.createRichTextString(reportname), excelUtils.CELL_STYLE_TITLE);

        rowIdx = rowIdx + 1;
        row = sheet.createRow(rowIdx); // empty row

        rowIdx = rowIdx + 1;
        row = sheet.createRow(rowIdx);
        int custId = salesForecastReportForm.getCustomerIds()[0];
        int psId = salesForecastReportForm.getProcessStepIds()[0];
        int spId = salesForecastReportForm.getSalesPersonIds()[0];

        excelUtils.setCellValue(row, 0, ch.createRichTextString("Customer:"), excelUtils.CELL_STYLE_HEADER);
        excelUtils.setCellValue(row, 1, custId <= 0 ? ch.createRichTextString("Any") : ch.createRichTextString(CustomerDAO.getCustomerName(null, custId) ), excelUtils.CELL_STYLE_HEADER);
        excelUtils.setCellValue(row, 2, ch.createRichTextString("Product:"), excelUtils.CELL_STYLE_HEADER);
        excelUtils.setCellValue(row, 3, psId <= 0 ? ch.createRichTextString("Any") : ch.createRichTextString(ProductDao.getDeviceName(psId) ), excelUtils.CELL_STYLE_HEADER);
        excelUtils.setCellValue(row, 4, ch.createRichTextString("Sales Person:"), excelUtils.CELL_STYLE_HEADER);
        excelUtils.setCellValue(row, 5, spId <= 0 ? ch.createRichTextString("Any") : ch.createRichTextString(SalesPersonSetupDAO.getSalesPersonName(null, spId) ), excelUtils.CELL_STYLE_HEADER);


        rowIdx = rowIdx + 1;
        row = sheet.createRow(rowIdx); // empty row

        rowIdx = rowIdx + 1;
        row = sheet.createRow(rowIdx);
        //SETTING HEADINGS
        List<String> monthsHeadings = new ArrayList<String>(monthsHeadingsMap.values());
        monthsHeadings.add(0, "           ");
        for (int i = 0; i < monthsHeadings.size(); i++) {
            cell = row.createCell(i);
            excelUtils.setCellValue(cell, ch.createRichTextString((String) monthsHeadings.get(i)), excelUtils.CELL_STYLE_HEADER);
            int width = (((String) monthsHeadings.get(i)).length() > 8) ? ((String) monthsHeadings.get(i)).length() : 8;
            sheet.setColumnWidth(i, (((width + 4) * 256)));
        }

        int nextRowNumber = rowIdx + 1;
        int datarowstartedidx = nextRowNumber;
        int nextColumnIdx = 0;
        for (int i = 0; i < forecastDataList.size(); i++) {
            RowData rowData = (RowData) forecastDataList.get(i);

            //for to insert 'Last Forecast' row before 'Actual Shipments' row.
            if("Actual Shipments".equalsIgnoreCase(rowData.getSnapshotName())) {

                row = sheet.createRow(nextRowNumber++); //empty row

                row = sheet.createRow(nextRowNumber++);
                nextColumnIdx = 0;
                excelUtils.setCellValue(row, nextColumnIdx++, ch.createRichTextString("Last Forecast"), excelUtils.CELL_STYLE_TEXT);
                Iterator<String> monthKeyItr = monthsHeadingsMap.keySet().iterator();
                while (monthKeyItr.hasNext()) {
                    monthKeyItr.next();
                    if(datarowstartedidx == (nextRowNumber-2)){
                        datarowstartedidx = nextRowNumber-3;
                    }
                    String formula = "IF(" + excelUtils.getCellReference(nextColumnIdx, datarowstartedidx) + "=\"\",0," + excelUtils.getCellReference(nextColumnIdx, datarowstartedidx) + ")";
                    datarowstartedidx++;
                    if (REPORT_TYPE_QUANTITY.equalsIgnoreCase(salesForecastReportForm.getReportType())) {
                        excelUtils.setCellFormula(row, nextColumnIdx++, formula, excelUtils.CELL_STYLE_NUMBER);
                    } else if (REPORT_TYPE_REVENUE.equalsIgnoreCase(salesForecastReportForm.getReportType())) {
                        excelUtils.setCellFormula(row, nextColumnIdx++, formula, excelUtils.CELL_STYLE_CURRENCY_$);
                    }
                }
            }

            row = sheet.createRow(nextRowNumber++);
            nextColumnIdx = 0;
            excelUtils.setCellValue(row, nextColumnIdx++, ch.createRichTextString(rowData.getSnapshotName()), excelUtils.CELL_STYLE_TEXT);
            Iterator<String> monthKeyItr = monthsHeadingsMap.keySet().iterator();
            while (monthKeyItr.hasNext()) {

                String itrValue = (String) monthKeyItr.next();
                if (itrValue.startsWith("Qty")) {
                    String[] split = itrValue.split("\\;");
                    int month = Integer.parseInt(split[1]);
                    int year = Integer.parseInt(split[2]);
                    SlotData slotData = rowData.getSlotData(month, year);
                    if (slotData != null)
                        excelUtils.setCellValue(row, nextColumnIdx++, slotData.getQty(), excelUtils.CELL_STYLE_NUMBER, Cell.CELL_TYPE_NUMERIC);
                    else
                        excelUtils.setCellValue(row, nextColumnIdx++, ch.createRichTextString((String)null), excelUtils.CELL_STYLE_NUMBER);
                } else if (itrValue.startsWith("Revenue")) {
                    String[] split = itrValue.split("\\;");
                    int month = Integer.parseInt(split[1]);
                    int year = Integer.parseInt(split[2]);
                    SlotData slotData = rowData.getSlotData(month, year);
                    if(slotData != null) {
                        String formula = slotData.getQty() + "*" + slotData.getPrice();
                        excelUtils.setCellFormula(row, nextColumnIdx++, formula, excelUtils.CELL_STYLE_CURRENCY_$);
                    } else {
                        excelUtils.setCellValue(row, nextColumnIdx++, ch.createRichTextString((String)null), excelUtils.CELL_STYLE_CURRENCY_$);
                    }
                }
            }

            //for to insert 'Forecast VS Backlog' row after 'Actual Backlog' row.
            if("Actual Backlog".equalsIgnoreCase(rowData.getSnapshotName())) {
                row = sheet.createRow(nextRowNumber++);
                nextColumnIdx = 0;
                excelUtils.setCellValue(row, nextColumnIdx++, ch.createRichTextString("Forecast VS Backlog"), excelUtils.CELL_STYLE_TEXT);
                monthKeyItr = monthsHeadingsMap.keySet().iterator();
                while (monthKeyItr.hasNext()) {
                    monthKeyItr.next();
                    String formula = excelUtils.getCellReference(nextColumnIdx, nextRowNumber-4) + "-" + excelUtils.getCellReference(nextColumnIdx, nextRowNumber-3) + "-" + excelUtils.getCellReference(nextColumnIdx, nextRowNumber-2);
                    if (REPORT_TYPE_QUANTITY.equalsIgnoreCase(salesForecastReportForm.getReportType())) {
                        excelUtils.setCellFormula(row, nextColumnIdx++, formula, excelUtils.CELL_STYLE_NUMBER);
                    } else if (REPORT_TYPE_REVENUE.equalsIgnoreCase(salesForecastReportForm.getReportType())) {
                        excelUtils.setCellFormula(row, nextColumnIdx++, formula, excelUtils.CELL_STYLE_CURRENCY_$);
                    }
                }
            }
        }
        return workbook;
    }
}
